Connecting to Access and updating a table from Excel

[vijer]

I would like to modify a QueryTable and update the Access database the recordset came from.

Is this possible and if so what is the best method to accomplish this?

Thanks

 

[Chance1234]

do you mean send data back to teh database or change the data you are receiving ?

If it is the latter then use MSquery

if it is sending data back

then

in VBA ditor

reference microsofts DAO 3.5


and your code might look something like this


Sub SendBackValuetoDB
Dim Mydb as database
Dim MyRec as recordset
Dim MyVal as variant


set mydb = "C:\Databases\BigDb.mdb"
set myrec = mydb.openrecordset("tablebig&quot

Myval = activesheet.range("a5&quot.value


with myrec
.add
![Date] = "22/9/03"
![Total] = Myval
.update
end with

set myrec = nothing
set mydb = nothing

 

[vijer]

Unfortunately when I try to run this code I get the error message

"User-defined type not defined"

and the Excel VBA editor highlights the dim statment

Dim Mydb As database

I have also tried

Dim Mydb As ADODB.Connection

and get the same error message

I am running Excel and Access 2000 on a W2K machine if that helps at all.

 

[TTThio]

In Access 2000,

dim MyDb as Dao.Database (or ADO.Database)
this applies in declaring recordset too.

 

[rhinoman]

If you are still looking, this worked for me.. add reference to Dao 3.6 in A2K.


Private Sub SendBackValuetoDB()

Dim Mydb As DAO.database
Dim MyRec As DAO.Recordset
Dim MyVal As Variant
Dim wrkJet As DAO.Workspace

Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)

Set Mydb = wrkJet.OpenDatabase("d:\temp\db1.mdb", False)



Set MyRec = Mydb.OpenRecordset("m_reference", dbOpenDynaset)

MyVal = ActiveSheet.Range("ReferenceNo&quot.Value


With MyRec
.AddNew
![ReferenceNo] = MyVal
.Update
End With

Set MyRec = Nothing
Set Mydb = Nothing


End Sub