Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2002
    Location
    NJ
    Posts
    139

    Unanswered: saving excel info in access

    Hi:

    win2k
    excel & access 2k

    I have users writing info into a sheet in excel and when clicking on the save button, I want to update an access db with that information. Is there a way I can open a db, write an sql to update that info and close the db from the excel save button?

    Here is what I have so far, but that is imcomplete

    Code:
    Sub Update_data()
    Dim MyCon As New Connection
    MyCon.Open "C:\logcall\logcall.mdb"
    Set rs = New Recordset
    ????
    rs.Open "Update logcall_table set clientname = range("b1").value where clientname = 'alex'"
    ????
    End Sub
    I have also found that in Access:

    Code:
    sub Update_data()
    dim db as database
    dim qdf as QueryDef
    dim strSQL as string
    ...
    .
    .
    .
    But that excel did not accept.

    Is there a way to do this?

  2. #2
    Join Date
    Oct 2004
    Posts
    8
    add a reference to MS DAO 3.6 Object Library
    the code to open a recordset is :

    Code:
       Dim db As DAO.Database
       Dim rs As DAO.Recordset
       
       Set db = OpenDatabase("c:\db1.mdb")
       Set rs = db.OpenRecordset("table name")
    
       .......enter code to insert data here
    
       rs.close
       db.close

Posting Permissions

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