Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004

    Question Unanswered: Update Access Table from Excel Sheet, ADO


    I am trying to Update a Access 2002 DB Table using the data from a Excel Sheet (Visual Basic 6).

    Dim cn As ADODB.Connection
    Dim rsT As ADODB.Recordset

    Set cn = New ADODB.Connection
    With cn
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .ConnectionString = "Data Source=" & "c:" & _
    "\b.xls;Extended Properties=" & """Excel 8.0; HDR=No;"""
    .CursorLocation = adUseClient
    End With
    Set rsT = cn.OpenSchema(adSchemaTables)

    rsT.Open "SELECT * FROM [Sheet1$]", cn

    ok with that I can get the data in Excel to a RS, great. Now I need to use SQL to update it, "UPDATE SET;" to update the access table.

    Why dont I use the rs.Update() method with a cn to the DB well that makes things complicated, and I can do it with the SQL faster and basically in one step, if I could set that part up.

    Help would be appriciated.

  2. #2
    Join Date
    Mar 2004

    Re: Update Access Table from Excel Sheet, ADO

    Are you updating a table in access or adding new records to a table in access?

    If you are adding new records do this:

    Do While not rsT.Eof

    cn.Execute "INSERT Table1(Field1) SELECT '" & rsT.fields("Field1") & "'"



    If you are updating existing records in a table do this (I am giving a simple linking example as I do not know how your recordset links to your table):

    Do While not rsT.Eof

    cn.Execute "UPDATE Table1 SET Field1='" & rsT.fields("Field1") & "' FROM Table1 WHERE Table1.Field2='" & rsT.fields("Field2") & "'"



    Download for FREE the ADO/DAO Data Controls that makes life EASIER developing database applications in: VB, FoxPro, Access, VC++, .NET etc... Navigate, Add New, Delete, Update, Search, Undo and Save your changes. Supports Disconnected Recordsets and Transactions!

    Or try our Ask An Expert service to answer any of your questions!

  3. #3
    Join Date
    Mar 2004

    thanks for your reply, yes im trying to update the recs, and u code works for that. when i saw it, i went - why didnt i think of that.

    boy did i feel stupid.

    again thanks alot for you input.


Posting Permissions

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