Query on dbase files with access and VBA

[Filou]

Hi everybody.
This is my first post in this newgroup I'm not a guru in Access

I have some 99 dbase 5 files in the folder C:\myFolder. Each dbase file is
named PoubX where X is 1 to 99. All these files have the same fields. I
also have a ms access table named Customer. The dbase files and the Access
table have a common field i call Join_Field.

What I want to do is
1- To import all the dbf records that match the condition (Field_1 =
Value_1 AND Field_2 = Value_2) and Customer.Join_Field = DbfFile.Join_Field.
The following query is the one I used in VBA for the first part of my goal.

For X = 1 to 99
myQuery = "SELECT * FROM Poub" & X & " IN " _
& Chr$(34) & Chr$(34) & "[dBASE IV; DATABASE=C:\myFolder;]" _
& " WHERE Field_1 = "Value_1" AND Field_2=Value_2 ;"
Next X

How can I implement the jointure ?

 

[MGFoster]

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

Perhaps this (just the string):

myQuery = "SELECT P.* " & _
"FROM Customer As C INNER JOIN Poub" & X & _
" IN ""[dBase IV;Database=C:\myFolder]"" As P " & _
"ON C.Join_Field=P.Join_Field " & _
"WHERE Field_1 = 'Value_1' AND Field_2=Value2"

For the string "Value_1" use single quotes inside the myQuery string.

Using 2 double-quotes inside a string expression evaluates to one double
quote. You need those quotes around the db identified (the IN clause).

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQWIEu4echKqOuFEgEQL3wACgois/s5Uq3Z2GCc9GLRlj6MMP2scAmgLn
3hkeT+BLUn8HcgoZrGFuO4Wt
=qFEU
-----END PGP SIGNATURE-----

 

[Filou]

Thank you for your help but it seems not to work. I get a runtime error
n°3131 -Syntax error in FROM clause-
I've output the value of strQuery and this is what I have

SELECT P.* FROM Customer AS C INNER JOIN Poub IN "[dBase
IV;Database=C:\myFolder;]" AS P ON C.Join_Field=P.Join_Field WHERE Field_1
= 'Value_1' AND Field_2='Value_2';

When I try to use this query in acces I receive an error around the IN
clause. May be this information can guide you.

Thank

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

Perhaps this (just the string):

myQuery = "SELECT P.* " & _
"FROM Customer As C INNER JOIN Poub" & X & _
" IN ""[dBase IV;Database=C:\myFolder]"" As P " & _
"ON C.Join_Field=P.Join_Field " & _
"WHERE Field_1 = 'Value_1' AND Field_2=Value2"

For the string "Value_1" use single quotes inside the myQuery string.

Using 2 double-quotes inside a string expression evaluates to one double
quote. You need those quotes around the db identified (the IN clause).

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQWIEu4echKqOuFEgEQL3wACgois/s5Uq3Z2GCc9GLRlj6MMP2scAmgLn
3hkeT+BLUn8HcgoZrGFuO4Wt
=qFEU
-----END PGP SIGNATURE-----

Hi everybody.
This is my first post in this newgroup I'm not a guru in Access

I have some 99 dbase 5 files in the folder C:\myFolder. Each dbase file
is named PoubX where X is 1 to 99. All these files have the same fields.
I also have a ms access table named Customer. The dbase files and the
Access table have a common field i call Join_Field.

What I want to do is
1- To import all the dbf records that match the condition (Field_1 =
Value_1 AND Field_2 = Value_2) and Customer.Join_Field =
DbfFile.Join_Field.
The following query is the one I used in VBA for the first part of my
goal.

For X = 1 to 99
myQuery = "SELECT * FROM Poub" & X & " IN " _
& Chr$(34) & Chr$(34) & "[dBASE IV; DATABASE=C:\myFolder;]" _
& " WHERE Field_1 = "Value_1" AND Field_2=Value_2 ;"
Next X

How can I implement the jointure ?

 

[MGFoster]

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

Ah... I looked at Jet reference manual & see that the SQL should look
like this:

SELECT P.*
FROM Customer AS C INNER JOIN Poub1
IN "" [dBase IV;Database=C:\myFolder;] AS P
ON C.Join_Field=P.Join_Field
WHERE Field_1='Value_1' AND Field_2=Value_2;

There are two (2) double-quotes and a space before the database type
(dBase IV). So the VBA string concat should look like this:

Const Q = """" ' One double-quote char: Chr$(34)

myQuery = "SELECT P.* " & _
"FROM Customer As C INNER JOIN Poub" & X & _
" IN " & Q & Q & " [dBase IV;Database=C:\myFolder;] As P " & _
"ON C.Join_Field=P.Join_Field " & _
"WHERE Field_1 = 'Value_1' AND Field_2=Value2"

You can also use this syntax:

myQuery = "SELECT P.* " & _
"FROM Customer As C INNER JOIN Poub" & X & _
" IN ""Database=C:\myFolder;"" ""dBase IV;"" As P " & _
"ON C.Join_Field=P.Join_Field " & _
"WHERE Field_1 = 'Value_1' AND Field_2=Value2"


--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQWNESoechKqOuFEgEQJLqACg+1rtTHsyBIYJ5uZBoEbxqmyS5bAAoOKM
X6INtPMmpTZL+SjgDaPEc4Lt
=Yu2+
-----END PGP SIGNATURE-----

Thank you for your help but it seems not to work. I get a runtime error
n°3131 -Syntax error in FROM clause-
I've output the value of strQuery and this is what I have

SELECT P.* FROM Customer AS C INNER JOIN Poub IN "[dBase
IV;Database=C:\myFolder;]" AS P ON C.Join_Field=P.Join_Field WHERE Field_1
= 'Value_1' AND Field_2='Value_2';

When I try to use this query in acces I receive an error around the IN
clause. May be this information can guide you.

Thank

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

Perhaps this (just the string):

myQuery = "SELECT P.* " & _
"FROM Customer As C INNER JOIN Poub" & X & _
" IN ""[dBase IV;Database=C:\myFolder]"" As P " & _
"ON C.Join_Field=P.Join_Field " & _
"WHERE Field_1 = 'Value_1' AND Field_2=Value2"

For the string "Value_1" use single quotes inside the myQuery string.

Using 2 double-quotes inside a string expression evaluates to one double
quote. You need those quotes around the db identified (the IN clause).

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQWIEu4echKqOuFEgEQL3wACgois/s5Uq3Z2GCc9GLRlj6MMP2scAmgLn
3hkeT+BLUn8HcgoZrGFuO4Wt
=qFEU
-----END PGP SIGNATURE-----

Hi everybody.
This is my first post in this newgroup I'm not a guru in Access

I have some 99 dbase 5 files in the folder C:\myFolder. Each dbase file
is named PoubX where X is 1 to 99. All these files have the same fields.
I also have a ms access table named Customer. The dbase files and the
Access table have a common field i call Join_Field.

What I want to do is
1- To import all the dbf records that match the condition (Field_1 =
Value_1 AND Field_2 = Value_2) and Customer.Join_Field =
DbfFile.Join_Field.
The following query is the one I used in VBA for the first part of my
goal.

For X = 1 to 99
myQuery = "SELECT * FROM Poub" & X & " IN " _
& Chr$(34) & Chr$(34) & "[dBASE IV; DATABASE=C:\myFolder;]" _
& " WHERE Field_1 = "Value_1" AND Field_2=Value_2 ;"
Next X

How can I implement the jointure ?