Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2003
    Location
    Melbourne, AUS
    Posts
    3

    Unanswered: SQL Insert from Excel into Access

    Hi,

    I am currently working on a Excel Tool that inserts data from Excel to an Access Database via a SQL Query in EXCEL/VBA.

    The code works on my home PC, however at work i don't have access to the ADO Object becuase i don't have developer rights and am not a developer, are there any other methods that can be used to insert data from excel to Access via SQL apart from the ADO Control and will work if you are a normal Excel user(i.e DAO, OLEDB and/or ODBC)



    Can somone please help me asap and provide a solution, help will be much appreciated, and i am more than happy to share my experiences/skills in return of this favour.

    P.s i am using Excel 2000
    Thanks very much

    Jabir

    email:jabir_jonuzi@yahoo.com.au


    The code that works with ADO.. (the Access db is just a flat file with 2 fields)




    Private Sub CommandButton1_Click()
    Dim strSQLCommand1 As String
    Dim strCnn As String
    Dim cnn1 As ADODB.Connection
    Dim var1 As String
    Dim var2 As String

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




    ' Define two SQL statements to execute as command text.


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


    Adodc1.CommandType = adCmdText

    strCnn = "DSN=test1"
    Set cnn1 = New ADODB.Connection

    ' Open connection.
    cnn1.Open strCnn


    cnn1.Execute strSQLCommand1
    On Error Resume Next
    cnn1.Close


    Adodc1.Refresh
    MsgBox " Record Inserted"

    End Sub

  2. #2
    Join Date
    Jul 2003
    Location
    Hong Kong
    Posts
    56

    Re: SQL Insert from Excel into Access

    You can simply create a link table in Access to open the excel file direcly.
    Cheers,
    Shev

  3. #3
    Join Date
    Jul 2003
    Location
    Hong Kong
    Posts
    56
    Hi jonuzi,

    Use Jet engine to avoid installation of Access on client side. You can use either DAO or ADO. Just wonder why you can't use ADO?
    I will prefer using ADO. ADO Code will be like this:

    Dim Conn As ADODB.Connection
    Dim strConn As String
    Dim strSQL As String

    Set Conn = New ADODB.Connection

    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Sample.mdb"

    strSQL = "<insert statement here>"

    Conn.Open strConn
    Conn.Execute strSQL
    Set myConn = Nothing
    Cheers,
    Shev

Posting Permissions

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