Re: Query on dbase files with access and VBA

From: MGFoster (me_at_privacy.com)
Date: 10/06/04 

Date: Wed, 06 Oct 2004 01:03:08 GMT

-----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-----
Filou wrote:
> 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
> 
> 
> "MGFoster" <me@privacy.com> a écrit dans le message de news: 
> 6xn8d.2954$M05.361@newsread3.news.pas.earthlink.net...
> 
>>-----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 wrote:
>>
>>>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 ?
>>
> 
>

Relevant Pages

  • Re: ADO Database Name Syntax
    ... My problem with that method is my dbase file always changes names with the ... Dim cnn As ADODB.Connection ... Dim sPath As String, sFile As String, sSQL As String ...
    (microsoft.public.access.formscoding)
  • Delegate question
    ... customer, and now I want to set a delegate up to Notify of any new ... was simple because I just instantiate the Delegate after i create the ... Private Sub init() ... Dim idx As String ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Want to Protect my Software - Recommendations?
    ... license keys. ...  If the license contains personal information, the customer ... One more important thing is that Microsoft decided not to use ... Basically you need to then have your software generate a unique string ...
    (sci.crypt)
  • Re: Multi-tier ASP.net web application
    ... I have a table "Customer" in the database. ... Dim password As String ... Public Sub New ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Multi-tier ASP.net web application
    ... Usually in Java applications, I created objects out of the database, ... I have a table "Customer" in the database. ... Dim password As String ...
    (microsoft.public.dotnet.framework.aspnet)
Quantcast