I need to link to a MS Access table from within MS SQL 1

[ITShaman]

I have a client that has a large Access db.&nbsp;&nbsp;They are installing MS SQL and will be installing an app that will use the MS SQL db.&nbsp;&nbsp;However, the MS SQL app will need to get information from the Access db.<br><br>I want to replicate or copy the data I need from the Access db to the MSSQL db.&nbsp;&nbsp;I know I can use an import/export mechanism (Access&gt;&gt;export to export.csv&gt;&gt;import into MSSQL) but this is a manual method.<br><br>I know that Access can work with ODBC to connect to an external MSSQL db table.&nbsp;&nbsp;Can I do the reverse and have an MSSQL db table poll data from an Access table?&nbsp;&nbsp;All I need is for MSSQL to read from Access; MSSQL does not need to write to Access.<br><br>Is there a mechanism to do this (via ODBC or other), and if so, how would it work?<br><br>Please reply to this forum or to my email address at <A HREF="mailto-pavl@sympatico.ca">p-pavl@sympatico.ca</A>.<br><br>Thank you in advance for any help.

 

[foxdev]

You can use the DTS Wizard to do this pretty quickly:<br><br>From the SQL Server program group, choose Import and Export Data, select the appropriate ODBC connection to your Access database in the Source section, then select the SQL Server as the destination.<br><br> <p>Robert Bradley<br><a href=mailto: > </a><br><a href=

http://www.foxdev.com/>FoxDev

- Visual FoxPro Development</a><br>

 

[Guest_imported]

Your Access code can keep the SQL table updated.

 

[Marinus]

I have used ODBC using Data Transformation Services to import data from and Access database. I did not use polling however. I would like to use polling however my access database is structured so that a single record accumulates data over the current day. What I did was to set the scheduler to start at the end of the business day to import the data into the sql database. I first had to create an ODBC connection (system DSN) to the local host of the access database ie. planer_run -&gt;\\planer\data\planer.mdb.<br>I then created a database using the access database structure by selecting new under Database (at the bottom of the screen). I then set the defaults to 10MB for data size and 1 MB for Log size. I then copied tables accross and VOILA ! I had a new SQL database that matched my Access database. I then used the Import or Export Data wizard again and this time I did not create a new database I selected the new sql database as the destination and that was it !

 

[Guest_imported]

another way to actually link to msaccess tables is to create a linked server via jet oledb. this requires that your access table must reside in the same server where sql server is installed.&nbsp;&nbsp;<br>

 

[Guest_imported]

Does anyone know how to set up a scheduled DTS task for an access database that is on another server?&nbsp;&nbsp;The DTS package executes perfect manually but it never seems to fire on a scheduled task.&nbsp;&nbsp;I'm assuming it's because the database is on another server.&nbsp;&nbsp;Any ideas?