Opening/running query against external MDB

[cttechsupport]

My quandry at the moment is a method to open a recordset from an
external file (MDB) so that the RS can be parsed and various
conditional tests checked along the way as WELL AS field updates when
various conditions are met.
For example (again invalid syntax - concept only) we will try an
"IN" clause with a Select statement......

Dim DB as DAO.database
Dim RS as DAO.Recordset
DIm SqlStr as string
set DB = currentDB()
SqlStr= "Select [field1], [field2], [field3] from [tablename] IN
'externalMDBname'' where [field3]= somevalue;"
Set RS = DB.Openrecordset(SqlStr)
RS.Movefirst
Do While not RS.EOF parse the recordet
select case [field2]
case 1 look for certain conditions and if
found update that record
.edit
rs.[field1] = newvalue
.update
case 2 again check conditions and make
updates accordingly
.edit
rs.[field1] = valueother
rs.[field3] = "X"
.update
end select
rs.movenext
Loop
rs.close
etc etc.........

When I try to run a code sequence like this I get a variety of
errors at the Set RS = DB.Openrecordset(SqlStr) line. The errors
vary from "Invalid Database Format" to "File not found" depending on
what minor tweaks I make to the syntax. For example, if I specify the
external database with it's full UNC name - 'c:\foldername
\databasename.MDB' I will get a "File not found" error. If I drop off
the "C:" and just put "\foldername\databasename.MDB" I will get an
Invalid Database Format" error !!!
Bizzare stuff indeed !!

Will be forever in debt (as I am many times over) to anyone who can
unravel this puzzle !!

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I believe you have to use double quotes, instead of single quotes, in
the SELECT statement; and you have to double them ;-) :

SqlStr= "Select [field1], [field2], [field3] from [tablename] IN
""externalMDBname"" where [field3]= somevalue;"

MS recommends that you attach the table instead of using IN; it's
supposed to be more efficient and faster. Use the TransferDatabase
method. To drop the table after use just use the .Delete method of the
TableDefs collection:

CurrentDB.TableDefs.Delete "[tablename]"

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBR74AJoechKqOuFEgEQJ5IQCfa379OH31IRzaAvUI+mUvH6ZH8rAAoIgJ
TztwUvKlWqo3EQJW8Jv/OUfN
=+jRC
-----END PGP SIGNATURE-----