IS this possible in Access/VB

Ok I need to know if I am doing something incorrectly or if this just isn't possible:

I have my main DB running in SQL Server 2000 on my ISPs machine.
I need to copy the tables to an Access DB on my machine.

I can copy the data by opening two connections, creating a recordset and looping through
inserting to the access db. But this is very slow.

What I'd like to do is:
Drop the table in question from the local access db : No problems here
Connect to the remote SQL db : No problems here either

Then I would like to create a new table in the access db using the following select statement:

strSQL = "SELECT tblOld.* INTO NewTable IN 'D:\Path\OfflineDB.mdb' FROM tblOld;"

When I run the statement I get the following error:

Run-Time Error -2147217900 (80040e14):
Incorrect Syntax Near Keyword 'IN'

Is this just something that cannot be done in Access (2000)?



LVL 2
gerryjcAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

JNSTAUBCommented:
if your db is in access format on your server ,just create an admin page protected by password with a link to the db to download .easy!!
or create a connectio n, create a table in your local access db and update the created table by an update query.
0
gerryjcAuthor Commented:
The database is not in Access format on the server.

The reason I am trying to do it this particular way is that it would eliminate the need to redistribute the database to all my client machines if I happened to make a small change to db structure.
0
gerryjcAuthor Commented:
OK I think I've Realised Whats Going on Here,

It may be that the command is actually trying to create the table in the path specified on the remote machine...hence the error code for a permissions error!

I don't suppose anyone would know a way around this problem?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

HelgeGudmundsenCommented:
If you are using Access, you could try using DoCmd.TransferDatabase to get the table copied:

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

I am using this command to do what you want, dropping a local table and copying an external table. In my case I am connecting to another Access database, but if you have an ODBC-connection to the SQL-server, the same method should work for you.

In my program the actual call to do this looks like this:

DoCmd.TransferDatabase acImport, "Microsoft Access", strPath, acTable, strSingleTable, strSingleTable

The VBA helpfile has this example showing how to link an external table via an ODBC-link.

DoCmd.TransferDatabase acLink, "ODBC Database", _
    "ODBC;DSN=DataSource1;UID=User2;PWD=www;LANGUAGE=us_english;" _
    & "DATABASE=pubs", acTable, "Authors", "dboAuthors"

HTH
0
bhagyeshtCommented:
why dont you just import the tables to access directly? using an SQL server driver and odbc which points to the server?
0
gerryjcAuthor Commented:
THe problem is that thi sis being executed from within a VB 6.0 application, I don't want the user to have anything to do with it.
0
bhagyeshtCommented:
I guess you can do that from vb too.
create a dsn and use access.application
then use docmd and transferdatabase
0
swift99Commented:
I do this all the time between DB2 and Access.  I also use Access to do this between two DB2 databases.

You need to link to your external table from your access database, then the query becomes

insert into destinationTable
select *
from LinkedExternalTable

or alternatively if it is a new table

select *
from linkedExternalTable
into destinationTable
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gerryjcAuthor Commented:
Thanks for that swift 99.

Worked for me.

In the end I used a DSN to the external database, linked my tables using that dsn and then used the select statement to create the new tables.

As an added bonus, by using a File dsn, Access appears to store the dsn info internally with the linked tables, meaning I do not need to redistribute a dsn with my app.

Cheers.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Programming

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.