Link to home
Start Free TrialLog in
Avatar of nainey
nainey

asked on

Importing a SQL Server 2000 Table

I want to import an SQL table into Access 97 in code and I keep getting an error "Could not find installable ISAM". If I import through the menu, i.e. File, Get External Data, Import it works. The code to import the table is below:

    DoCmd.TransferDatabase acImport, "ODBC", "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Neve;Data Source=2000SERVER\SERVER2000", acTable, "studdet", "studdetnew", False

Nainey
Avatar of Arthur_Wood
Arthur_Wood
Flag of United States of America image

why are you IMPORTING the table into your Access DB.  I would suggest that you LINK the SQL Server Table into your Access DB. That way, any new data ADDED or CHANGED in the SQL Server Table is automatically available to you in your Access Database, without you having to import the data again.

A LINKED table looks, to your application, EXACTLY like any other local table.

If you are not familiar with LINKED tables, then just ask for more information.

Hi nainey,

Either case (import or link), you should first define an ODBC connection via "Control Panel-> ODBC 32 Bit". Make sure that you create a file DNS connection rather than a system DNS connection.

HTH,

Nosterdamus
Avatar of KMAN
KMAN

Why the File DSN vs. System DSN?
Avatar of nainey

ASKER

Nosterdamus,
I have already defined a file DSN ans it is still not working!
Avatar of nainey

ASKER

Arthur Wood,
I am familiar with Linked tables and already use them, but this table has to be imported because I specifically do not want the changes made to be automatically updated. Anyway, how can I link the table through code - if I just chnage acImpoprt to acLInk I get the same error.
Thanks
Nainey
ASKER CERTIFIED SOLUTION
Avatar of kellykln
kellykln

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of nainey

ASKER

Indeed my connection string was wrong. Thanks
Nainey