Need help to save record from excel vba userform to Access database

[manissinha]

hi

i have create a userform in excel for multiple user to use all at a time and i want to save its input record into Access database. i am able to save it on excel but if multiple user user at same time to save record it giving error , that's why i want to use Access ,
please help to to save record into Access database

 

[ranman256]

You CAN make an Access front end for everyone to use simultaneously.
but
if you want, you can make an ADO connection to the access backend using an Excel front end entry.
The user clicks SAVE button , then run code....

sub Save_click()
Dim conn as new ADO.connection

conn.connectionstring= ".....your data..."

SSql=" insert into table (field1,field2) values ('" & txtBox1 & "'," & txtBox2 & ")"
Conn.execute sSql
Conn.close
set conn= nothing
end sub

 

[alansidman]

crossposted: Need help to save record from excel vba userform to Access database

 

[Macropod]

manissinha: Please read Mr Excel's policy on Cross-Posting in rule #13: http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html

 

[VBABEGINER]

Hi ranman..

I am getting an error on this..
Could please help me solve this..
Error getting.."operation is not allowed when object is closed"
Even thogh i kept access db open.

Dim conn As New ADODB.Connection
Dim sSql


conn.connectionstring = "\\group$\Common\Dashboard\Pankaj\H&B\Timesheet_Data.accdb"


sSql = " insert into Table1 (Start Date/Time,Closed Date/Time,Activity Type,Sub Type,Case ID,EE Tool TimeDate,Project ID,Project Name,Task ID,Task Name,Comments,User name) values ('" & txtstartdate & "',DateTime.Now, " & cmbActivity & "," & ComboBox1 & "," & TxtCaseID & "," & TxtEETime & "," & TxtProjectID & "," & TxtProjectName & "," & TxtTaskID & "," & TxtTaskName & "," & cmbTaskStatus & "," & txtcomm & ")"
conn.Execute sSql
conn.Close
Set conn = Nothing

You CAN make an Access front end for everyone to use simultaneously.
but
if you want, you can make an ADO connection to the access backend using an Excel front end entry.
The user clicks SAVE button , then run code....

sub Save_click()
Dim conn as new ADO.connection

conn.connectionstring= ".....your data..."

SSql=" insert into table (field1,field2) values ('" & txtBox1 & "'," & txtBox2 & ")"
Conn.execute sSql
Conn.close
set conn= nothing
end sub

 

[xenou]

Dim conn As New ADODB.Connection
Dim sSql


conn.connectionstring = "\\group$\Common\Dashboard\Pankaj\H&B\Timesheet_Data.accdb"


sSql = " insert into Table1 (Start Date/Time,Closed Date/Time,Activity Type,Sub Type,Case ID,EE Tool TimeDate,Project ID,Project Name,Task ID,Task Name,Comments,User name) values ('" & txtstartdate & "',DateTime.Now, " & cmbActivity & "," & ComboBox1 & "," & TxtCaseID & "," & TxtEETime & "," & TxtProjectID & "," & TxtProjectName & "," & TxtTaskID & "," & TxtTaskName & "," & cmbTaskStatus & "," & txtcomm & ")"
conn.Execute sSql
conn.Close
Set conn = Nothing

Hi,
You never opened the connection anywhere in this code. The ADO connection.

 

[VBABEGINER]

Can you help me in the right code..

 

[Micron]

Isn't what's going on here called hijacking a thread? Isn't that supposed to be discouraged? I realize it's remotely possible that a year later the OP has changed their handle, but probably not the case here.

 

[xenou]

I used to use the examples here:
http://www.erlandsendata.no/english/index.php?d=envbadacexportado

you can also use this site (and many others):
https://www.datawright.com.au/excel_resources/excel_access_and_ado_part_2.htm