2 thoughts on “Contact”

1. Comment?

   Reply ↓

2. I cam across this code to update access table from VBA.

   It works fine but this need to have the values supplied on the code.
   Copy to clipboard
   ” VALUES (‘John’,’Smith’,42)”
   Is it possible to amend the code to have the values from Excel Cells say Range A1 to A3

   Copy to clipboard
   Sub INSERT_to_Table()
   ‘!!!!!! Add Reference to Microsoft ActiveX Data Objects 2.x Library!!!!!!

   Dim strConnectString As String
   Dim objConnection As ADODB.Connection
   Dim strDbPath As String
   Dim strTblName As String
   Dim strSQL As String
   Dim ErrorMessage

   ‘Set database name and DB connection string——–
   strDbPath = ThisWorkbook.Path & “\TestDB.accdb”
   ‘==================================================
   strConnectString = “Provider = Microsoft.ACE.OLEDB.12.0;” & _
   “Data Source=” & ThisWorkbook.Path & “\TestDB.accdb;” & _
   “Jet OLEDB:Database Password=’****’;”

   ‘& _
   ‘ “Mode=Share Exclusive”
   ‘ strConnectString = “Provider = Microsoft.ACE.OLEDB.12.0; data source=” & strDbPath & _
   ‘ “;MS Access;PWD=asdf”
   ‘Connect Database; insert a new table
   Set objConnection = New ADODB.Connection
   On Error GoTo ErrorMessage

   With objConnection
   .Open strConnectString
   .Execute “INSERT INTO TEST” & _
   ” (FirstName,LastName,Age)” & _
   ” VALUES (‘John’,’Smith’,42)”
   MsgBox “Records Updated.” & nowtime
   End With

   Set objConnection = Nothing

   Exit Sub

   ErrorMessage:
   MsgBox “OPIS: ” & Err.Description & Chr(10) & _
   “NUMER: ” & Err.Number
   Set objConnection = Nothing

   End Sub
   My actual spreadsheet has 30 columns, so wanted to have the values received from the excel sheet

   Also, would be nice if we could avoid to enter the column header on the code
   Copy to clipboard
   ” (FirstName,LastName,Age)” & _
   as it has 30 columns but vba to automatically get from column 1 to 30

   Reply ↓