I have 2 tables which separately exists on Access mdb (Customer) and SQL
server 2000 (Orders)

Is there a way for me to join these 2 tables in one query?

Thanks

Connect to your SQL Server and use OpenRowSet to get data from Access. See
help on OpenRowSet for syntax and examples.

--


HTH

Éric Moreau, MCSD, Visual Developer - Visual Basic MVP
(http://aspnet2.com/mvp.ashx?EricMoreau)
Conseiller Principal / Senior Consultant
Concept S2i inc. (www.s2i.com)
http://pages.videotron.com/emoreau/

[quoted text, click to view]

well you can

1: use openrowset in a Transact sql statement

example :
USE pubs
GO
SELECT a.*
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'c:\MSOffice\Access\Samples\northwind.mdb';'admin';'mypwd', Orders)
AS a
GO

2: create a linked table in Access ( i.o.w . a connection to the sql
server table you wish to use ) you can use it as a normall local table in
your query

regards

Michel Posseth [MCP]




[quoted text, click to view]

Great! It works like a charm.

Unfortunately, when I want to move one more step further.... I encountered:
"Error converting data type DBTYPE_DBTIMESTAMP to datetime."

while I tried to run below in the SQL Query Analyzer
SELECT * from Openquery(access_mdb, 'select confirm_date from orders')


[quoted text, click to view]