If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
the best way to talk to MS ACCESS
Hi,
On ms excel I have a sub to send a sql command to ms access Dim objAcc As Object Set objAcc = CreateObject("Access.Application") objAcc.OpenCurrentDatabase "D:\1CNC\APPS\CNC_newapp.mdb" With objAcc.Application .Visible = False '.DoCmd.RunMacro ("Macro1") .DoCmd.RunSQL "DELETE FROM ttools where tid=7878 and pid=4324" .DoCmd.Quit End With Is the best way to do it? The db in question will be always open and that particular table will be always open Thank you all!!! Bre-x |
#2
|
|||
|
|||
the best way to talk to MS ACCESS
If all you're trying to do is delete data from the mdb file, there's no need
to instantiate another instance of Access. Try simply DoCmd.RunSQL "DELETE FROM [;Database=D:\1CNC\APPS\CNC_newapp.mdb].ttools where tid=7878 and pid=4324" or (better in my opinion, since it doesn't pop up confirmation windows, plus will raise a trappable error if something goes wrong) CurrentDb.Execute "DELETE FROM [;Database=D:\1CNC\APPS\CNC_newapp.mdb].ttools where tid=7878 and pid=4324", dbFailOnError -- Doug Steele, Microsoft Access MVP http://www.AccessMVP.com/DJSteele (no e-mails, please!) "Bre-x" wrote in message ... Hi, On ms excel I have a sub to send a sql command to ms access Dim objAcc As Object Set objAcc = CreateObject("Access.Application") objAcc.OpenCurrentDatabase "D:\1CNC\APPS\CNC_newapp.mdb" With objAcc.Application .Visible = False '.DoCmd.RunMacro ("Macro1") .DoCmd.RunSQL "DELETE FROM ttools where tid=7878 and pid=4324" .DoCmd.Quit End With Is the best way to do it? The db in question will be always open and that particular table will be always open Thank you all!!! Bre-x |
#3
|
|||
|
|||
the best way to talk to MS ACCESS
Thank you for answering my post!!
I am not only going to delete records but also modify some information "UPDATE......" on MS Excel I have modified my sub Sub Temp() DoCmd.RunSQL "DELETE FROM [;Database=D:\1CNC\APPS\CNC_newapp.mdb].ttools where tid=7878 and pid=4324" end sub I get an error: Run-time 424 Object requiered. Thanks again!!! "Douglas J. Steele" wrote in message ... If all you're trying to do is delete data from the mdb file, there's no need to instantiate another instance of Access. Try simply DoCmd.RunSQL "DELETE FROM [;Database=D:\1CNC\APPS\CNC_newapp.mdb].ttools where tid=7878 and pid=4324" or (better in my opinion, since it doesn't pop up confirmation windows, plus will raise a trappable error if something goes wrong) CurrentDb.Execute "DELETE FROM [;Database=D:\1CNC\APPS\CNC_newapp.mdb].ttools where tid=7878 and pid=4324", dbFailOnError -- Doug Steele, Microsoft Access MVP http://www.AccessMVP.com/DJSteele (no e-mails, please!) "Bre-x" wrote in message ... Hi, On ms excel I have a sub to send a sql command to ms access Dim objAcc As Object Set objAcc = CreateObject("Access.Application") objAcc.OpenCurrentDatabase "D:\1CNC\APPS\CNC_newapp.mdb" With objAcc.Application .Visible = False '.DoCmd.RunMacro ("Macro1") .DoCmd.RunSQL "DELETE FROM ttools where tid=7878 and pid=4324" .DoCmd.Quit End With Is the best way to do it? The db in question will be always open and that particular table will be always open Thank you all!!! Bre-x |
#4
|
|||
|
|||
the best way to talk to MS ACCESS
I totally missed the fact that you were trying to call this from Excel.
What you've got is probably appropriate, although you might get better advice if you ask in an Excel-related newsgroup. -- Doug Steele, Microsoft Access MVP http://www.AccessMVP.com/DJSteele (no e-mails, please!) "Bre-x" wrote in message ... Thank you for answering my post!! I am not only going to delete records but also modify some information "UPDATE......" on MS Excel I have modified my sub Sub Temp() DoCmd.RunSQL "DELETE FROM [;Database=D:\1CNC\APPS\CNC_newapp.mdb].ttools where tid=7878 and pid=4324" end sub I get an error: Run-time 424 Object requiered. Thanks again!!! "Douglas J. Steele" wrote in message ... If all you're trying to do is delete data from the mdb file, there's no need to instantiate another instance of Access. Try simply DoCmd.RunSQL "DELETE FROM [;Database=D:\1CNC\APPS\CNC_newapp.mdb].ttools where tid=7878 and pid=4324" or (better in my opinion, since it doesn't pop up confirmation windows, plus will raise a trappable error if something goes wrong) CurrentDb.Execute "DELETE FROM [;Database=D:\1CNC\APPS\CNC_newapp.mdb].ttools where tid=7878 and pid=4324", dbFailOnError -- Doug Steele, Microsoft Access MVP http://www.AccessMVP.com/DJSteele (no e-mails, please!) "Bre-x" wrote in message ... Hi, On ms excel I have a sub to send a sql command to ms access Dim objAcc As Object Set objAcc = CreateObject("Access.Application") objAcc.OpenCurrentDatabase "D:\1CNC\APPS\CNC_newapp.mdb" With objAcc.Application .Visible = False '.DoCmd.RunMacro ("Macro1") .DoCmd.RunSQL "DELETE FROM ttools where tid=7878 and pid=4324" .DoCmd.Quit End With Is the best way to do it? The db in question will be always open and that particular table will be always open Thank you all!!! Bre-x |
#5
|
|||
|
|||
the best way to talk to MS ACCESS
Thanks,
I would like to hear your opinion on this also. The CNC_newapp.mdb is the front end, I have the CNC_tables.mdb. so when I call the function it goes to the front end and from it, It goes to the back end. Woud it be better if I run my code agains the back end? Thank you!!! "Douglas J. Steele" wrote in message ... I totally missed the fact that you were trying to call this from Excel. What you've got is probably appropriate, although you might get better advice if you ask in an Excel-related newsgroup. -- Doug Steele, Microsoft Access MVP http://www.AccessMVP.com/DJSteele (no e-mails, please!) "Bre-x" wrote in message ... Thank you for answering my post!! I am not only going to delete records but also modify some information "UPDATE......" on MS Excel I have modified my sub Sub Temp() DoCmd.RunSQL "DELETE FROM [;Database=D:\1CNC\APPS\CNC_newapp.mdb].ttools where tid=7878 and pid=4324" end sub I get an error: Run-time 424 Object requiered. Thanks again!!! "Douglas J. Steele" wrote in message ... If all you're trying to do is delete data from the mdb file, there's no need to instantiate another instance of Access. Try simply DoCmd.RunSQL "DELETE FROM [;Database=D:\1CNC\APPS\CNC_newapp.mdb].ttools where tid=7878 and pid=4324" or (better in my opinion, since it doesn't pop up confirmation windows, plus will raise a trappable error if something goes wrong) CurrentDb.Execute "DELETE FROM [;Database=D:\1CNC\APPS\CNC_newapp.mdb].ttools where tid=7878 and pid=4324", dbFailOnError -- Doug Steele, Microsoft Access MVP http://www.AccessMVP.com/DJSteele (no e-mails, please!) "Bre-x" wrote in message ... Hi, On ms excel I have a sub to send a sql command to ms access Dim objAcc As Object Set objAcc = CreateObject("Access.Application") objAcc.OpenCurrentDatabase "D:\1CNC\APPS\CNC_newapp.mdb" With objAcc.Application .Visible = False '.DoCmd.RunMacro ("Macro1") .DoCmd.RunSQL "DELETE FROM ttools where tid=7878 and pid=4324" .DoCmd.Quit End With Is the best way to do it? The db in question will be always open and that particular table will be always open Thank you all!!! Bre-x |
#6
|
|||
|
|||
the best way to talk to MS ACCESS
I doubt you'd see any meaningful difference.
-- Doug Steele, Microsoft Access MVP http://www.AccessMVP.com/DJSteele (no e-mails, please!) "Bre-x" wrote in message ... Thanks, I would like to hear your opinion on this also. The CNC_newapp.mdb is the front end, I have the CNC_tables.mdb. so when I call the function it goes to the front end and from it, It goes to the back end. Woud it be better if I run my code agains the back end? Thank you!!! "Douglas J. Steele" wrote in message ... I totally missed the fact that you were trying to call this from Excel. What you've got is probably appropriate, although you might get better advice if you ask in an Excel-related newsgroup. -- Doug Steele, Microsoft Access MVP http://www.AccessMVP.com/DJSteele (no e-mails, please!) "Bre-x" wrote in message ... Thank you for answering my post!! I am not only going to delete records but also modify some information "UPDATE......" on MS Excel I have modified my sub Sub Temp() DoCmd.RunSQL "DELETE FROM [;Database=D:\1CNC\APPS\CNC_newapp.mdb].ttools where tid=7878 and pid=4324" end sub I get an error: Run-time 424 Object requiered. Thanks again!!! "Douglas J. Steele" wrote in message ... If all you're trying to do is delete data from the mdb file, there's no need to instantiate another instance of Access. Try simply DoCmd.RunSQL "DELETE FROM [;Database=D:\1CNC\APPS\CNC_newapp.mdb].ttools where tid=7878 and pid=4324" or (better in my opinion, since it doesn't pop up confirmation windows, plus will raise a trappable error if something goes wrong) CurrentDb.Execute "DELETE FROM [;Database=D:\1CNC\APPS\CNC_newapp.mdb].ttools where tid=7878 and pid=4324", dbFailOnError -- Doug Steele, Microsoft Access MVP http://www.AccessMVP.com/DJSteele (no e-mails, please!) "Bre-x" wrote in message ... Hi, On ms excel I have a sub to send a sql command to ms access Dim objAcc As Object Set objAcc = CreateObject("Access.Application") objAcc.OpenCurrentDatabase "D:\1CNC\APPS\CNC_newapp.mdb" With objAcc.Application .Visible = False '.DoCmd.RunMacro ("Macro1") .DoCmd.RunSQL "DELETE FROM ttools where tid=7878 and pid=4324" .DoCmd.Quit End With Is the best way to do it? The db in question will be always open and that particular table will be always open Thank you all!!! Bre-x |
#7
|
|||
|
|||
the best way to talk to MS ACCESS
"Bre-x" wrote in
: The CNC_newapp.mdb is the front end, I have the CNC_tables.mdb. so when I call the function it goes to the front end and from it, It goes to the back end. Woud it be better if I run my code agains the back end? I would say use the back end directly, but only if you're using only tables and not queries stored in the front end. -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ |
Thread Tools | |
Display Modes | |
|
|