Hi everyone,
I have an excel spreadsheet (TEST_RT)when it is refreshed, I need to have it sent to an Access database, destroy the old table (if there), create a new one and dump four columns of the spreadsheet data in the table. Or better yet, don't destroy the table, just overwrite any data if the Name and date/time matches and append/update if it is new data.
The four columns are from the "RTQ" worksheet; the columns are Name, Date/time, value1,value2.
(The database I ultimately want to dump the data in is an Ingres database and can query the database through excel no problem, it is just the insert to ingres part I am finding hard, so I figured I'd start with Access, sooooo much easier!! )
Any help would be greatly appreciated!!!
Export four columns of a Spreadsheet to Access using VBA
subramaniac
Hello,
One way, and there are probably a few different ways to do this, would be to execute SQL statements against the database using the ADO Command object (or the DAO equivalent).
ADO is more modern so I'd use that. From Excel you'll need to add a reference to the Microsoft ActiveX Data Objects Library. Once you do that you'll get access to the Command object.
Dim adoCmd As New ADODB.Command
adoCmd.CommandType = adCmdText
adoCmd.CommandText = "DELETE * FROM [Table]"
adoCmd.ActiveConnection.ConnectionString = "[YOUR DATABASE CONNECTION STRING]"
adoCmd.Execute
That example deletes all records from the table... you would loop over the rows in Excel executing an INSERT statement against the database for each row.
If you want to do the update as a batch you'll need to use the Recordset object but I'm not to familiar with how to do that.