SLQ to view a remote data surce

[silkep]

I hove some MS Access MDB located in different serves and I would like to connect all thorough SQL server 7.0 because I cannot import them directly.

It's possibile to visualize these DBs in a SQL view?

thanks

S. Persson

 

[cmmrfrds]

I don't know about a view, but you can link the mdb's to SQL Server at least in SQL Server 2000. You would need to make sure that you have the correct permissions on the various servers, both from an NT and SQL Server point of view. After the servers/mdbs are linked you could write stored procedures to select data from the various databases. The stored procedures can be record sources on your forms.

If you need further assistance, post in the SQL Server Form.

 

[silkep]

I got a good result with this simple SQL command inserted in a table view:

Select a.* from openrowset ('Microsoft.Jet.OLEDB.4.0','\\server\folder\db_1.mdb';;,table_1) as a WHERE field_1=xxx"

just one little problem... it'a quite slow to execute.

any other idea?

 

[tlbroadbent]

Use ap_addlinked server to add a link for the Access database. Then use OpenQuery rather than OpenRowset. The OpenQuery should run faster than OpenRowset. However, querying an Access MDB will always be slower than querying a SQL table. sp_addlinkedserver and OpenQuery are well documented in SQL BOL with examples using an Access database.

One thing to keep in mind no matter which method you use. The SQL server account login must have permissions to read the MDB on the other server. You should use the UNC to make the connection as suggested by Silkep. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.