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

    Question Unanswered: Update Access Table from Excel Sheet, ADO

    Hi,

    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
    .Open
    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
    Location
    www.scirocco.ca
    Posts
    346

    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:

    rsT.MoveFirst
    Do While not rsT.Eof

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

    rsT.MoveNext

    Loop

    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):

    rsT.MoveFirst
    Do While not rsT.Eof

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

    rsT.MoveNext

    Loop
    http://www.scirocco.ca/images/banner...occobanner.gif

    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
    Posts
    6
    SCIROCCO,

    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.

    -VX-

Posting Permissions

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