Macro/Module Issues

[student]

Since this is coming from an Excel starting point, please point me elsewhere if needed, but was hoping a kind soul could assist as Google isn't helping me enough today...

I have a 'dashboard' in Excel which calls through a number of queries from an Access DB which in turn pulls data from various linked tables (from Sage etc).

Within this Access DB I have an Append query for a table which I then pull through to Excel to give required results.

What I want is for the EU to click a button in the Excel sheet to run the Append query then pull through the resultant table. Better still would be to pull through just the data Appended to the table to save update time. If the update and the data pull have to be done separately to ensure stability and enough time to append table then so be it, but clearly if EU can do this with one button press so much the better.

Code I've attempted to 'borrow' from SOS on a virtually identical thread ( http://www.access-programmers.co.uk/forums/showthread.php?p=941647 )
is

Function RunAppendQuery(strDatabaseName As String, strAppendQueryName As String)
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
         Set db = OpenDatabase(strDatabaseName)
         Set qdf = db.QueryDefs(strAppendQueryName)
 
            qdf.Execute
 
            qdf.Close
 
    db.Close
    Set qdf = Nothing
    Set db = Nothing
End Function

And I've attached a macro to a button just calling this function.

Anyone fancy teaching me where I've gone wrong? It's all pretty new to me

Using Office 2007 & Win7

------

Edit: Sorry guys, forgot to post the error message.

Run-time error '3343':
Unrecognised database format '.........accdb'

When debug is chosen it highlights line:

Set db = OpenDatabase(strDatabaseName)

 

[Privateer]

Student,
Just a guess here, but the DAO was for Access 2002 and earlier, ADO afterward, unless the references were specifically changed back to DAO. Try switching that and make sure the commands "execute" and "close" work in ADO. Change the ADO first, then delete the word "execute" and the period, then type in the period and see if they appear as valid commands.
Good Luck
Privateer

 

[carl6885]

Do you have an example of the file path you are trying to pass into the function?

Was the DB built in 2007 or 2010? Even though 2007 is accdb it sometimes wont open accdb created in 2010 giving the error message above.

Can I ask why you are using a function also? A sub routine would be suitable as you are not returning anything.

Thanks

Carl

 

[vbaInet]

Was the DB built in 2007 or 2010? Even though 2007 is accdb it sometimes wont open accdb created in 2010 giving the error message above.

carl is right on the money there... no upward compatibility per se. So if you're better off opening all your databases using Access 2010 if you don't want to encounter this problem.