Send data from XL to Access using VB?

[marknixon]

Does anyone know how I can send variables extracted from userform textboxes in XL to an Access database and enter them in the relevant fields of a new record? Can you create a DDE connection between XL and Access?

 

[RoyVidar]

Hi!

Here's a routine that opens a connection to an Access Database and inserts the lyric "Some value" to a specific table.

To make it work, you'l need to go to Tools | References and be sure "Microsoft ActiveX Object Library 2.1" or something similar is checked.

Sub test()
Dim rs As ADODB.Recordset
Dim oConn As ADODB.Connection

Set oConn = New ADODB.Connection
oConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"User ID=Admin;" & _
"Data Source= c:\test.mdb"
oConn.Open
Set rs = New ADODB.Recordset
With rs
.ActiveConnection = oConn
.LockType = adLockOptimistic
.CursorType = adOpenDynamic
.Open "tblTest", Options:=adCmdTable
.AddNew
!field1 = "Some value"
.Update
.Close
End With
Set rs = Nothing
Set oConn = Nothing
End Sub

HTH Roy-Vidar

 

[marknixon]

Cheers Roy.

One small problem. When I ahev the line: oConn.open after the oConn.connection.string, I get the error message "Operation is not allowed when the object is closed".

SO I moved the oConn.open line above the other line and the error message then read "Data source name not found and no default driver specified"

Seems like it can't run one line without the other first. Both ways.

Also, in the connection.string line, is the data source (c:\test.mdb in your example) meant to be the directory of the database into which I'm sending data?

Thanks for the help.

Mark