Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Linking Access to Simply Accounting with VBA

Status
Not open for further replies.

SaturnBiomed

IS-IT--Management
Joined
Aug 12, 2005
Messages
9
Location
CA
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.
 
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
 
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?
 
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
 
I think it's just called "Simply Accounting: (My Accounting Database Filename)".
 
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
 
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
 
In the ODBC Manager (i.e. when I say to do an external database query) it says "Simply Accounting: May12 Master.sdb"
 
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!
 
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
 
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top