the best way to talk to MS ACCESS

B

Bre-x

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
 
D

Douglas J. Steele

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
 
B

Bre-x

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 said:
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 said:
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
Click to expand...
Click to expand...
 
D

Douglas J. Steele

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 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
B

Bre-x

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 said:
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 said:
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 said:
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!)

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
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J. Steele

I doubt you'd see any meaningful difference.

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

Bre-x said:
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 said:
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 said:
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!!!







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!)

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
Click to expand...
Click to expand...
Click to expand...
 
D

David W. Fenton

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?
Click to expand...

I would say use the back end directly, but only if you're using only
tables and not queries stored in the front end.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

MS Access 2007 & .vbs script -> opened read-only 12
Best way to deal with record-locking conflicts 2
The best way to refer to grid column? 1
Export Access query to Excel via Macro 1
MS Access opens the last opened application every time I launch it 4
Displaying MS Access reports 2
Best way to display a chart on MS Access 97 form 4
Best way to work with ADO.net and MS Access 7

Top