Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2003
    Melbourne, AUS

    Unanswered: SQL Insert from Excel into Access


    I have developed a Excel Application that inserts data from Excel to an Access Database via SQL.

    The code works on a PC with Access, however the users whom i am building this app for don't have MS Access.

    There are around 5 users who will be submitting data from their Excel file into the .mdb.(without MS Acccess on thier PC's)

    Can somebody please help me, i believe that i can use the Jet engine to insert data from Excel to the MDB file without MS Access on the PC, however i don't know how this can be done, don't know the code that's involved.
    *Also please note that i can't use the ADO object as i have restricted access to this object at work.

    Please help asap, appreciate help on this and i am keen on sharing my experiences and skills with others.

    Here is the code that inserts data from Excel to the .mdb file with MS Access.

    The Test.mdb file is just a file consisting of 2 fields(name,surname)


    Sub SQLInsert1()

    ''strSQLCommand1 = "INSERT INTO test VALUES ('" & var1 & "', '" & var2 & "')"

    Dim appAccess As Object
    Dim strConPathToSamples As String
    Dim strDB As String
    Dim var1 As String
    Dim var2 As String

    var1 = Sheets("sheet1").Cells(1, 1)
    var2 = Sheets("sheet1").Cells(1, 2)

    ' Initialize string to database path.
    strConPathToSamples = "\\\jonuzij1 $\My Documents\M-drive data\Decision Support System Project\Merchant\v1\test.mdb"

    strDB = strConPathToSamples
    MsgBox strDB

    ' Create new instance of Microsoft Access.
    Set appAccess = _
    ' Open database in Microsoft Access window.
    appAccess.OpenCurrentDatabase strConPathToSamples
    ' Open Orders form.
    appAccess.DoCmd.RunSQL "(insert into test values('" & var1 & "', '" & var2 & "'))"

    End Sub

  2. #2
    Join Date
    Sep 2003
    Hi jon

    The interface to Jet is known as Data Access Objects (DAO). Your users should have this installed already. Under Tools...References look for 'Microsoft DAO Object Library 3.6' or whatever version. Make sure your users have the same version too. You can look up DAO programming in the online help files, but here's some sample code to get you started:

    Sub AddToMDB()

    Dim db As DAO.Database
    Dim rs As DAO.Recordset

    Set db = DAO.DBEngine.OpenDatabase("C:\Program Files\Microsoft Office\Office10\Samples\northwind.mdb")
    Set rs = db.OpenRecordset("Customers", dbOpenDynaset)

    rs!CustomerID = "ANYCO"
    rs!CompanyName = "A New Co"
    rs!City = "AnyTown"
    rs!Region = "BigArea"
    rs!PostalCode = "12345"

    Set db = Nothing
    Set rs = Nothing

    End Sub



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts