By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,635 Members | 2,243 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,635 IT Pros & Developers. It's quick & easy.

Excel Macro export data to specific Access Record

P: 9
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
Share this Question
Share on Google+
3 Replies

P: 9
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

P: 9
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

P: 9
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.