Thread: OpenDatabase and Run Query from Excel not working

Hi All,

I am trying to create VBA in Excel 2003 to open an Access 2003 db, and run a MakeTable Query. I'm not sure if I'm using the wrong vba commands for Excel, or what the problem is- but I can't get my code to work:

Sub RunQuery()
DoCmd.OpenDatabase "S:\path\file.mdb"
db.TableDefs.Delete "2010DataSet"
db.Execute "MakeDataTable2010"
db.Close
Set db = Nothing
End Sub

I'm getting a runtime error 424 'Object Required' on the DoCmd line. I thought the database was the object?

For the record, I don't need the Excel users to see the database, so I'd like to add a visible=false in there somewhere.

Any help is appreciated, I get completely lost when I try to cross programs in VBA

Hello AnnK,

If this macro is being run from Excel then you need to create an object instance of Access before you can use the Access commands, like DoCmd from Excel.

Hello Annk,

Glad you like my name. Do you know anyone named Leith?

I am not an Access programmer and this getting beyond my area of knowledge. I can do a couple of things for you. Because this is more Access centric, I could move this post to the Access forum for you where you may have a better chance of getting an answer faster or refer your post to some of the moderators who have experience with Access or just leave your post here and hope someone will pick it from here. What would you like to do?

hi AnnK,

I'm not an Access wizz either but I've been learning quite a lot in my latest job, so I'll see if I can help...
(Leith, I hope this doesn't mess up any of your Mod work)

Here's a couple of UNTESTED changes that may help:

Public Sub RunQuery3()
dim accApp as object 'it's good practice to declare variables & it appears you are using late binding so I've made it an Object. You can change to early binding* by adding a Reference to Access via Excel's VBE - Tools - References - ticking the "Microsoft Access xx.x Object Library" reference.
'*(a Google search will bring up a few discussions comparing the two approaches)
dim accDb as object 'ditto
Dim DoesTableExist As Boolean

    Set accApp = CreateObject("Access.Application")
    accApp.Visible = False
    accApp.DoCmd.SetWarnings False
    set accDb = accApp.OpenCurrentDatabase ("S:\path\file.mdb")


On Error Resume Next ' I'm not sure if the error bypass is needed...?
 DoesTableExist = IsObject(accDb.TableDefs("2010DataSet"))
On Error GoTo 0  ' Turn Error trapping back on

If DoesTableExist Then
    'you have: accApp.TableDefs.Delete "2010DataSet"
'but shouldn't it be relating to the db not the app? For example,
accDb.TableDefs.Delete "2010DataSet"
end if

with accApp
.DoCmd.OpenQuery "MakeDataTable2010"
'perhaps add a line here to ".close" the database before the following line which quits the instance of Access 
.Quit
end with
End Sub

Re the Security Message:
Security's normally in place for a reason & some types of discussion are prohibited on this Forum, but if I understand correctly (please provide more detail about the message), I think that setting your "Trusted Locations" may be sufficient. Here's one link which explains Trusted Locations: http://msdn.microsoft.com/en-us/libr...ffice.12).aspx

hth
Rob

I thought I may have made a booboo.

hmmm...
What's the exact wording of the "the numeric overflow message on open query line"?
Have you defined some of the fields in the Table as Integer, and does the query tries to populate them with a value that is greater than +/- 32k?

Does it work if you go back to the ".Execute "MakeDataTable2010"" version?
Or if you try any of the other suggestions in this link?

What's the query's sql string?
Does it work "manually" if you run it in Access?
Does it work "manually" if you attempt to use it in Excel via Data - From Access - choose file...?

LOL, yep they do just seem to be there for the legal dept. How big is your company?
My office mate & I went through 50ish people to set up their Trusted Locations (in excel, Ppt & Access). It's a hassle but only needs to be done once - unless someone gets a major IT overhaul on their computer.


Rob

Aha!! I found the problem that was causing the "Numeric Field Overflow" message. I did find that when I ran the query from Access it worked fine, so I did some more searching on the error message. It turns out it is caused by there being mixed data types in the spreadsheet, which when it is linked to Access (which mine is) causes the error upon querying the linked table (but apparently only when the query is initiated from outside of Access). Microsoft provides the solution at

http://support.microsoft.com/kb/815277

(I hope it's okay to post a link that is from Microsoft as opposed to a competing board)

Basically the solution was to run a macro that converts the cells in the mixed columns to text. I already knew that one of my columns was a problem and had done that- but it turns out I had a second column with some entries that were numeric in among the text.

This also allowed me to lose the line about deleting the table before running the query. I thought that I needed it to address the overflow, but since I fixed the text issue, Access just overwrites the table- so it's unneeded. My final -working- code ends up being

Public Sub RunQuery()
    Set accApp = CreateObject("Access.Application")
    accApp.Visible = False
    accApp.DoCmd.SetWarnings False
    accApp.OpenCurrentDatabase ("S:\path\file.mdb")
    accApp.DoCmd.OpenQuery "MakeDataTable2010" 
'get numeric field overflow on this line if there are mixed text columns
' run the numeric overflow fix if error occurs
    accApp.Quit
End Sub

Thank you for all the help!!

hi AnnK,

Thank you for posting your solution, marking the thread as Solved & giving me some rep'

Originally Posted by AnnK

...
http://support.microsoft.com/kb/815277

(I hope it's okay to post a link that is from Microsoft as opposed to a competing board)

Absolutely!
If it wasn't for Microsoft, you wouldn't have the application & therefore you wouldn't have the problem - let alone be asking questions on a Forum.
One of the aims of the Forum is to share knowledge & that's all you're doing
The rule relating to links to other forums explicitly refers to links "in signatures" & when the rule was published (19/01/2010) I believe the intent was to stop spammers using their signatures as "free advertising". Numerous comments have been made about this Rule, but its implementation still riles me... *counting to ten...*

So, all I request, is that you continue to share relevant knowledge in posts where you can - whether the solution is from this, or other Forums.

Rob