How do Import an ODBC table using VBA?

[SunnyByfleet]

I can import a table manually from Sage into Access. How can I do this programatically?

 

[Blorf]

Sage? Are you using mas200 or mas90? If so, instead of importing your table, link it, then you can query it with code. Also, if mas, there is a mas 90 forum here also.

ChaZ

 

[SunnyByfleet]

I have no idea what MAS200 or MAS90 is. I use Sage Line 100 with Swan.

However, there are two reasons I want to import rather than link.

Firstly, if I query the linked tables the application has a tendancy to intermittently freeze. Fort instance, I query three joined tables, and when the query runs properly it takes about 20 minutes. Half the time however, it will just hang. I left a machine running it, and it finally finished some 17 hours later. When it hangs on a system, you can break out, then rerun it, and it might only take 20 minutes.

Run the same query on local tables and its much much quicker plus it doesn't hang.

(Now, if you can come up with a solution to that one then I'm all ears!)

The second reason, is that one of my apps works with an offline copy of some of the data. Its for the sales team and they need it when they are on the road. In this instance a query of the live data isn't possible, so they need an offline copy. The plan is every week they are at the office, and they will push a button and the data will get imported. This is the other reason why I want the VBA way.

Any ideas?

I have tried searching the forum but it keeps telling me it is undergoing maintenance.

 

[Leeeon]

The solution is to copy the contents of the whole table as a simple Make Table query:

First create an ODBC link to the Sage table (I've used 'scheme_stockm' in this example)

Then create a Make Table query to import this data to a new table (I've called new table 'Stockm_Import')

SQL:

SELECT scheme_stockm.* INTO Stockm_Import
FROM scheme_stockm;


This may take a while to run depending in your link and size of table you are importing, but shouldn't be any longer than directly importing a table.

Another solution is to create a report in Sage with just the fields you require, and make it export the data to text file. Make type 'I', and use character separator '@'. Then import that text file. This is much quicker but requires you to run the report prior to importing and is a bit more fiddly to setup.

 

[sostek]

I'm curious - why import it using VBA when you can just link the table. That being said - there is a DoCmd method that loads table contents via an ODBC link I just can't recall the method at the moment.

 

[SunnyByfleet]

Sostek, read the 2nd post I made. All is revealed...

Currently I've got the following:

doCmd.SetWarnings False
'This gets read of the "Are you sure you want to import..." messages
docmd.RunSQL "INSERT INTO [LOCAL_FILE] SELECT * FROM [ODBC_FILE]"
doCmd.SetWarnings True

This does the job, but strikes me as messy. Perhaps I'm being over-sensitive?

That still leaves me having to delete the table first. Is there a way to delete a table but leave the structure? Currently I have to delete each record which I'm sure is long-winded.

Thanks

 

[PHV]

That still leaves me having to delete the table first. Is there a way to delete a table but leave the structure?
DoCmd.RunSQL "DELETE FROM [LOCAL_FILE]"

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