Hi,
Here's the background of the problem: I maintain an Excel data table with a new row for every new day. Many calculations are done with this data, which get presented monthly. The data also gets uploaded to an Access database each day using a 1-click button and VBA macro. The existing code for establishing a connection to the Access database looks like this:
I'm creating an updated version of this table and the corresponding calculations and delivery. I would like to connect to the Database in the same way, and only update the method of selecting the data for upload (currently it uploads manually-selected data; I want it to upload the latest data automatically).
The problem is this: When I copy over the code for establishing a connection to the database as above, it does not work. I get the following error:
The upload to the Access db works from the old excel workbook, but not the new one, even with the exact same VBA code for establishing a connection.
Any help deciphering why this error occurs would be greatly appreciated!
forwork
Here's the background of the problem: I maintain an Excel data table with a new row for every new day. Many calculations are done with this data, which get presented monthly. The data also gets uploaded to an Access database each day using a 1-click button and VBA macro. The existing code for establishing a connection to the Access database looks like this:
Code:
' Define variables for connection, recordset, a counter, and the lastrow
Dim cn As ADODB.Connection, rs As ADODB.Recordset, i As Long, lastrow As Long
' connect to the Access database
Set cn = New ADODB.Connection
'define and open a recordset from the specified database path and database table name. Change it to suit your need.
cn.Open "Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=[path]\prices.accdb"
Set rs = New ADODB.Recordset
rs.Open "Prices", cn, adOpenKeyset, adLockOptimistic, adCmdTableI'm creating an updated version of this table and the corresponding calculations and delivery. I would like to connect to the Database in the same way, and only update the method of selecting the data for upload (currently it uploads manually-selected data; I want it to upload the latest data automatically).
The problem is this: When I copy over the code for establishing a connection to the database as above, it does not work. I get the following error:
The upload to the Access db works from the old excel workbook, but not the new one, even with the exact same VBA code for establishing a connection.
Any help deciphering why this error occurs would be greatly appreciated!
forwork
Last edited:
