i heard already from the way to use the Sql Server Export/Import Manager to export a sql table to a ms access file, but this is not dynamically to use, is there a way to do it in a stored procedure? Like execute a query and save this as table in a ms access file, like the Export/Import Manager but just in code?
Thanks in advance!
You can probably create and save a SSIS package to do the import/export task from Sql Server to Access and call it from your stored procedure with the required parameter.
I suppose there are several things you can try. If you have access to SSIS, this will do the work for you, for sure. You can use the Import/Export wizard to help you with the task.
Configure and Test the Linked Server
Connect to the SQL Server and create a new linked server. This can be done using the following SQL script, which will create a new liked server called "AccessDB." (The value after @datasrc is the path to the Access database on the computer that is running SQL Server.)
EXEC sp_addlinkedserver
@server = 'AccessDB',
@provider = 'Microsoft.Jet.OLEDB.4.0',
@srvproduct = 'OLE DB Provider for Jet',
@datasrc = 'C:\Path\to\Access\Database.mdb'
GO
Test the linked server by selecting from an existing table in the Access database (this is optional). For example, if there is a table called "Table1" in the Access database, the following SQL script will select all data from it.
SELECT *
FROM OPENQUERY(AccessDB, 'SELECT * FROM Table1')
If necessary, you can remove the linked server using the sp_dropserver procedure.
EXECUTE sp_dropserver 'AccessDB'
Importing Data into the Access Database
To import data into an existing table in the Access database, the INSERT OPENQUERY(...) syntax must be used. For example, if the SQL Server table is called SrvTable and the Access table is called Table1 and both tables contain the same columns, the following SQL script will copy the data from the SQL Server table into the Access table.
INSERT OPENQUERY(AccessDB, 'SELECT * FROM Table1')
SELECT * FROM SrvTable
If necessary, you can also update existing data in the Access database from the SQL Server using the UPDATE OPENQUERY syntax, as seen below.
UPDATE OPENQUERY(AccessDB, 'SELECT * FROM Table1')
SET Col1 = 'Testing...'
You can also delete data from the Access database using the DELETE OPENQUERY syntax. (Note the escaped single-quotes [''] inside the OPENQUERY statement.)
DELETE OPENQUERY(AccessDB, 'SELECT * FROM Table1 WHERE Col1 = ''Testing...''')
Finally, create a stored procedure that utilizes any combination of the OPENQUERY statements to accomplish your data import task. For example, to copy all records from the SrvTable to Table1 the Access database, then update Col1 to "Testing...", use the following SQL script.
CREATE PROCEDURE CopyToTable1 AS BEGIN
INSERT OPENQUERY(AccessDB, 'SELECT * FROM Table1')
SELECT * FROM SrvTable
UPDATE OPENQUERY(AccessDB, 'SELECT * FROM Table1')
SET Col1 = 'Testing...'
END
Finally, consider this option....
Follow These Steps: (Video also available)
1. Open control Panel
2. open “ODBC” from “Administrative tools”
3. Create a Data Source for the SQL database from which you need to import
4. open MS Access, Select “External Data” Tab
5. Select “ODBC Database”
6. In “Machine Data source” tab, select the Data Source you have created.
7. Then you will find an Import Object window, Select the tables that you need to Import
8. Then Press “OK”
If you prefer to do using ADO : http://www.vb-helper.com/howto_ado_import_sql_server.html
Else use this code:
SELECT * FROM [ODBC;Driver=SQL Server Native Client 10.0;SERVER=Your_Server_Name;DATABASE=DB_Name;UID=User_Login;PWD=Your_Password]
