How to import table from SQL Server 2000 into Access

[cs211024]

Hi forks,

I am wondering is there some way to import table from sql server 2000 into access?

I tried the import / export wizard -> ODBC, seems it not works.

Any hint or advice is appreciated.

Morgan

 

[BNPMike]

Usually you dump (or unload, or whatever they call it in SQL Server) the table to a file, and then load the file. The Access load utility is called 'import'.

 

[dhookom]

SQL Server has DTS which allows you to graphically specify data flow from one data source to another. However the easiest method would be to create an ODBC/DSN for the database and table on SQL Server and then link or create a pass-through to the table from Access. Your statement "ODBC, seems it not works" is a bit vague.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[cs211024]

Thanks BNPMike and dhookom's reply.

I used the control panel->ODBC to set up system dsn linked to the table data of sql server, and then import table using this ODBC. I got the error message is like "MS ACCESS can not import data from this ODBC".

Is this approach a effective and corrective way? If it is, then I will need to find something reason else to fix this problem.

 

[dhookom]

When you set up the ODBC data source, did you test it? Can you link to the table rather than trying to import it? Is there anything unusual about the table? Do you have permissions to select from the table?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.