Contact

The contact page has been removed because of a lot of spam incoming on my mailbox.
Would like to share with me some important info? Leave a comment on this page. I will contact you 😉

2 thoughts on “Contact

  1. 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

Leave a Reply

Your email address will not be published.