JetSQL syntax using external Db with LEFT JOINs

[bildun]

I've got the following bit of code I'm trying to work with to get a JOIN to work in like fields in two mdb files with ASP. What I have below just gives me a syntax error on the FROM clause. I'm trying to JOIN the users table in the users.mdb database with the username field in the videolog database.

Set adoConn = Server.CreateObject("ADODB.Connection")

adoConn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Mid(Request.ServerVariables("PATH_TRANSLATED"), 1, Len(Request.ServerVariables("PATH_TRANSLATED")) - Len(Request.ServerVariables("PATH_INFO"))) & "\Dbs\users.mdb;Persist Security Info=False"

strSQL = "SELECT * FROM videolog IN 'c:\html\domain\Dbs\videos.mdb' LEFT JOIN users ON videolog.username = users.username"

 

[PHV]

Just a guess (typed, untested):
strSQL = "SELECT * FROM videolog LEFT JOIN users IN 'c:\html\domain\Dbs\videos.mdb' ON videolog.username = users.username"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[bildun]

I tried that and I get the same error - Syntax error in FROM clause.

 

[PHV]

Have you tried to play with DAO instead of ADO ?
Another way is to play with linked tabmes and thus have a single mdb to query.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[bildun]

I'm not familar with DAO. In this particular situation I have a mdb file for logging and such that exists only on the web site and another mdb file that is uploaded daily from a clients accounting application.

I figure I could "simulate" a JOIN with an array instead but I'd rather do that as a last resort.

 

[Golom]

strSQL = "SELECT * FROM videolog LEFT JOIN [;database=c:\html\domain\Dbs\videos.mdb].users 
ON videolog.username = users.username"

 

[bildun]

That worked. Here's the final working version. Thanks for the help.

<%
Set adoConn = Server.CreateObject("ADODB.Connection")
adoConn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Mid(Request.ServerVariables("PATH_TRANSLATED"), 1, Len(Request.ServerVariables("PATH_TRANSLATED")) - Len(Request.ServerVariables("PATH_INFO"))) & "\Dbs\users.mdb;Persist Security Info=False"
strSQL = "SELECT * FROM members LEFT JOIN [;database=c:\html\domain\Dbs\videos.mdb].videolog ON videolog.username = members.username"
Set adoRec = adoConn.Execute(strSQL)

Do While not adoRec.EOF
Response.Write adoRec("members.username") & "<BR>"
adoRec.MoveNext
Loop
%>

 

[IT4EVR]

If you put this database in the same root folder as your web site on the server, then you can just use Server.MapPath("<dbname>.mdb") as your data source. Then create a link, like PHV suggested, to the log db.

The IN Syntax for queries tends to be slow in performance.

I find it makes it a lot easier.