Hi all,

I would like to import a table using VBA (thus making it automatic on opening main form). I have tried the code layout bellow

'TransferDatabase(TransferType, DatabaseType, DatabaseName, ObjectType, Source, Destination, StructureOnly, StoreLogin)

but have had no success ! to import the table manually these are the steps i follow :

1) File - Get External Data - Import

An import box opens then

2) i use the drop down box 'files of type' to find odbc

This then opens a box called 'select data source'

3) i select the 'Machine data source' tab

This gives me a list

4) i select SageLine50v10

I am then asked for a user name and password

5) Once i have entered the user name/ password I am given the import objects box

6) i now click on the table and press ok


THE TABLE IS NOW IMPORTED : -)

Is it possible to automate this process ???

any help would be fantastic !!!!1

Thanks
Martin

Did you ever get an answer to this Martin? I want to do the same.

If I link the sage table and perform queries on it, it can hang for literally hours if another user opens the table.

So I want to import the table into a local table and query that.

I can do it manually, but the users aren't so savvy.

I can fudge it by doing the following:

docmd.runsql "INSERT INTO [LOCALTABLE] SELECT * FROM [ODBCTABLE]"

This works but produces a dialog saying "You are about to insert blah blah"

Also, I have to delete all the records first.

I'm not happy about the above method, as it still uses SQL and might therefore still lock. It would be handy if I could call the routine that gets run when I do this manually.

Anybody have any ideas?

Hello Martin,

sounds as do-able but I am at a loss what ODBC db you are trying to connect to. (SageLine50v10???)

See the following:
http://www.able-consulting.com/ado_conn.htm?f=ado_conn.htm

Do you find your ODBC somewhere on the list?

Martin o!

He defined a system data source. Pretty much the same as creating a connectionstring, but instead it's stored locally on the user's machine. You can find them (in XP) by using administrative tools/Data Sources.

SageLine50v10 is just the name he gave to it.

To find the connectionstring to that particular database, see this snippet from the Access Help file:



Transfertype = acLink
DatabaseType = "ODBC Database"
DatabaseName = The connectionstring you found above. It should look something like this:



Since you mentioned you manually have to input the username/password, include them in the connectionstring as to remove that effort away from the user.

ObjectType = acTable
Source = "Authors"
Destination = "dboAuthors"

Hi Peter,

this is clear. I wanted to suggest usage of ADO but I need the connection string to suggest a working code.

Let's see what comes back from the OP.

Cheers,

Martin

Ah sorry

I figured you were unclear about it because of the name.

*stuffs foot in mouth*

Indeed I was

Thanks for your input

M.

Edited by: KingMartin on Wed Nov 17 5:07:00 EST 2004.