Linking Access to Simply Accounting with VBA

[SaturnBiomed]

The company I work for uses Simply Accounting Pro 2005 and I am building an MS Access database to supplement it.

Presently, to get the data into Access, I am first using Microsoft Query to get the tables into Excel, then I am linking the table to Access.

I am now working on the VB code to make the data refresh/updates happen automatically, or at the click of a button. I am a novice VB user so any help or guidance would be a real help.

I have gotten VB to refresh the data in Excel and that works great. But in using "DoCmd.TransferSpreadsheet acLink" it creates a new copy of the tables each time rather than updating the link. Can anyone tell me the command(s) to just update the link other than using the linked table manager?

My second/alternative question is... Is it possible to link directly from Simply Acc. into Access and skip the Excel stage? I have tried "DoCmd.Transferdatabase acLink..." but it doesn't recognize the format. I have looked everywhere online but I can't seem to find anyone who is doing this successfully. (Although many are trying.)

Any help would be appreciated.

 

[PHV]

If you can get the data with MS-Query you can directly link an access table with the same ODBC connection:
menu Files -> External data -> Link tables

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SaturnBiomed]

Hi, thanks for your suggestion. However, when I try that, it says:

"You cannot use ODBC to import from, export to, or link an external Jet or ISAM database table to your database."

For some reason, Excel (or rather, MS Query) can get at it but not Access. Any other ideas?

 

[PHV]

Which DSN are you using with MS-Query ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SaturnBiomed]

I think it's just called "Simply Accounting: (My Accounting Database Filename)".

 

[PHV]

And which type of database is an 'Accounting Database' ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SaturnBiomed]

It's a .SDB file

 

[PHV]

I meant in the ODBC manager.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SaturnBiomed]

In the ODBC Manager (i.e. when I say to do an external database query) it says "Simply Accounting: May12 Master.sdb"

 

[SaturnBiomed]

Do you have any suggestions on how to update the linked tables (from Excel)? If I can figure out how to do that, then I won't have to worry about all that ODBC stuff.

I'm getting really frustrated as I have already spent too much time on this data refresh program!

 

[PHV]

A quick google search makes me think that a SDB file is an access database.
In the menu Files -> External data -> Link tables you may try *.sdb as filename and then pick your May12 Master.sdb

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SaturnBiomed]

Nope, no luck. It says I don't have access, but it didnt even ask for a username or password. I have full access to the Simply Acc file and it is also set to allow 3rd party software to interact with it. I don't know why it would say that I don't have access.