468,119 Members | 1,759 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,119 developers. It's quick & easy.

Excel Macro export data to specific Access Record

I am trying to create an excel macro, that when clicked... a specific cell will match a record in my database, and update certain fields in this record.

The Excel file will have a cell that is the same as a 'Tracking Number' (the primary key) of my Access Table. The output from excel would access this record and update the field.

I currently have a macro to create new records in the table, but I am not sure if there is a way to update certain records.

Dec 10 '07 #1
3 3882
here is the code i have currently to add a new record:
Expand|Select|Wrap|Line Numbers
  1. Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long
  2.     ' connect to the Access database
  3.     Set cn = New ADODB.Connection
  4.     cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
  5.         "Data Source=c:\db1.mdb;"
  6.     ' open a recordset
  7.     Set rs = New ADODB.Recordset
  8.     rs.Open "ProjectInbound", cn, adOpenKeyset, adLockOptimistic, adCmdTable
  9.     ' all records in a table
  10.  With rs
  11.             .AddNew ' create a new record
  12.             ' add values to each field in the record
  14.             .Fields("Output") = Range("C10").Value
  16.             ' add more fields if necessary...
  17.             .Update ' stores the new record
  18.  End With
  20.  With Application
  21.         .ScreenUpdating = False
  22.         .DisplayAlerts = False
  23.     End With
Dec 10 '07 #2
I found this clause in some other code, which may be what I am looking for, but I cannot figure out the context
Expand|Select|Wrap|Line Numbers
  1. .Open "select * from c_test where c_testid = '" & labelID.Caption & "'"
Dec 10 '07 #3
I also found this bit of code
Expand|Select|Wrap|Line Numbers
  1. rs.Find("Tracking Number=" & Range("A1").Value)
but I get this error:
"arguments are of the wrong type..."
Dec 10 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Steven Stewart | last post: by
17 posts views Thread by Mansi | last post: by
10 posts views Thread by Niklas | last post: by
13 posts views Thread by didacticone | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.