Click here to Skip to main content
15,918,471 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hello.

How can I copy or export a table from the SQL Server DB to an existing ACCESS DB?
Either with ADO or ADO.NET.

Many thanks for your help.

What I have tried:

I tried to use an OBBC DSN. Without success.
Posted
Updated 5-Feb-23 23:28pm

1 solution

As per Microsoft's tutorial found HERE, you can do the following to import a database from Sql Server into Access -

NOTE: When you import data, Access creates a one-time, copy of the data, and so any changes made to data after the import in either Access or the SQL Database are not synchronized.

1. To import -
Select External Data > New Data Source > From Database > From SQL Server.
In the Get External Data – ODBC Database dialog box select Import the source data into a new table in the current database.

Select OK.


2. Create or reuse a DSN file
You can create a DSN file or reuse an existing one. Use a DSN file when you want to rely on the same connection information for import operations or to share with a different application that also uses DSN files. You can create a DSN file directly by using the Data Connection Manager.

1. If the DSN file you want to use already exists, select it from the displayed list. Depending on which authentication method you entered in the connection information, you may need to enter a password again.
2. To create a new DSN file:
-Select New.
-Select ODBC Driver for SQL Server, and then select Next.
-Enter a name for the DSN file, or click Browse to create the file in a different location.
-Click Next to review the summary information, and then click Finish.


3. Use the Create a New Data Source to SQL Server wizard
In the Create a New Data Source to SQL Server wizard, do the following:
-On page one, enter identification information:
---In the Description box, optionally enter documentary information about the DSN file.
---In the Server box, enter the name of the SQL Server. Do not click the down arrow.

-On page two, select one of the following authentication methods:
---With Integrated Windows authentication    Connect through a Windows user account, OR
---With SQL Server authentication…    Connect with credentials that have been set up in the database by entering the login ID and password.

-On pages three and four, select various options to customize your connection.
-A screen appears to confirm your settings. Select Test Data Source to confirm your connection.
-You may need to login to the database. In the SQL Server Login dialog box, enter the login ID and password. To change additional settings, select Options.


4. Select tables and views to link to or import from
1. In the Link Tables or Import Objects dialog box, under Tables, select each table or view that you want to link or import, and then click OK.
2. In a link operation, decide whether to select Save Password.
3. Security -
---Selecting this option eliminates the need to enter credentials each time you open Access and access the data. But, this stores an unencrypted password in the Access database, which means people who can access the source contents can see the user name and password.


5. Create specifications and tasks
In the Get External Data - ODBC Database dialog box, you can save the import steps as a specification and create an Outlook task to automate the import operation on a regular basis.


6. End Result -
When an import operation completes, the tables appear in the Navigation Pane with the same name as the SQL Server table or view combined with the owner name.
Access never overwrites a table in the database. Although you cannot directly append SQL Server data to an existing table, you can create an append query to append data after you have imported data from similar tables.


When you get stuck, see the provided link to ensure that you have followed the steps 100%.
 
Share this answer
 
Comments
Member 14914301 6-Feb-23 6:25am    
Many thanks.
Andre Oosthuizen 6-Feb-23 6:37am    
It's a pleasure, if it helps as a solution, please mark it as solved, thanks.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900