I'm trying to add data from an Excel 2003 worksheet to an Access 2003 table using Microsoft Visual Basic 6.5 (the one that comes with Excel/Office). I'm able to read data from the Access table using the following code that I got using the macro recorder:
With Worksheets("Pin Data").QueryTables.Add(Connection:="ODBC;DSN=MS Access Database;DBQ=C:\Documents and Settings\tg715c\My Documents\My Work\Work Files\Connector Pin Reserve and Visualization Tool\Connector Pin Reserve and Visualization Tool DataBase.mdb;DefaultDir=Connector Pin Reserve and Visualization Tool DataBase;DriverId=25;FIL=MSAccess;MaxBufferSize=2048;PageTimeout=5;", Destination:=Sheets("Pin Data").Range("A2"))
.CommandText = "SELECT `Connectors RM445`.`From Equip Nbr`, `Connectors RM445`.`From Term Pos`, `Connectors RM445`.`Gage` FROM `Connectors RM445` WHERE (`Connectors RM445`.`From Equip Nbr` Like '" & ConnectorTextBox & "' AND `Connectors RM445`.`From Term Pos` <> '""' AND `Connectors RM445`.`From Term Pos` <> 'DED' AND `Connectors RM445`.`From Term Pos` NOT LIKE '=%') ORDER BY `Connectors RM445`.`From Term Pos`"
.Name = "Query from MS Access Database"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
My problem is that I also need to update the Access table (i.e. add new record) with data from Excel. Any help?