saving excel info in access

Alexxx12 · #1 (**permalink**) 09-14-04, 08:22

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?

n00bl0rd · #2 (**permalink**) 10-02-04, 10:58

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

Internet Services

Web Development

Digital Marketing

Consumer Tech