I believe this would be simpler if you would use
OPENROWSET[
^] and execute the statement in Sql Server. The query could look like
INSERT INTO SqlServerTableName (col1, col2, ...)
SELECT col1, col2, ...
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'path to the mdb file';
'admin';'',
AccessTableName);
If you don't want to use OPENROWSET you can create a
linked server
on the Sql Server which would point to the Access database.
In order to create a linked server, have a look at
sp_addlinkedserver[
^].
So creating the server could be something like
EXEC sp_addlinkedserver
@server = 'mydatainaccess',
@provider = 'Microsoft.Jet.OLEDB.4.0',
@srvproduct = 'OLE DB Provider for Jet',
@datasrc = 'path to the mdb file'
So you'd create this only once on the SQL Server and after that you can copy the data by executing the INSERT INTO SELECT statements on the Sql Server. Something like:
INSERT INTO SqlServerTableName (col1, col2, ...)
SELECT col1, col2, ... FROM mydatainaccess...AccessTableName