Aug 6, 2001 #1 vijer IS-IT--Management Joined Feb 20, 2002 Messages 3 Location US 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
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
Aug 6, 2001 #2 Chance1234 IS-IT--Management Joined Jul 25, 2001 Messages 7,871 Location US 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" Myval = activesheet.range("a5".value with myrec .add ![Date] = "22/9/03" ![Total] = Myval .update end with set myrec = nothing set mydb = nothing Upvote 0 Downvote
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" Myval = activesheet.range("a5".value with myrec .add ![Date] = "22/9/03" ![Total] = Myval .update end with set myrec = nothing set mydb = nothing
Aug 6, 2001 Thread starter #3 vijer IS-IT--Management Joined Feb 20, 2002 Messages 3 Location US 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. Upvote 0 Downvote
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.
Aug 6, 2001 #4 TTThio Programmer Joined May 3, 2001 Messages 185 Location US In Access 2000, dim MyDb as Dao.Database (or ADO.Database) this applies in declaring recordset too. Upvote 0 Downvote
Aug 23, 2001 #5 rhinoman Programmer Joined Aug 18, 2001 Messages 13 Location AU 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".Value With MyRec .AddNew ![ReferenceNo] = MyVal .Update End With Set MyRec = Nothing Set Mydb = Nothing End Sub Upvote 0 Downvote
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".Value With MyRec .AddNew ![ReferenceNo] = MyVal .Update End With Set MyRec = Nothing Set Mydb = Nothing End Sub