database query to upload a dbf file

[sajay]

DEAR ALL THIS IS SECOND POST

-------------------------------------------------------------------
SEPTEMBER 17
I want to upload data from two dbf files.
One file is the names
and the other is theire records with corresponding unique id
I want to show the records with name (not unique id) in one worksheet.
-------------------------------------------------------------------


My first file (dbf) has the following fields
CLIENT_ID,CLIENT_NAME,CONTACT_PERSON, TYPE

Next file (dbf) has following fields CLIENT_ID, BILL_DATE,BILL_AMOUNT,
WORK,WORK_DONE_BY.

I want to list these datas in to a Excel Sheet listing BILL DATE,
CLIENT NAME, BILL AMOUNT

instead of secondfile's CLIENT ID i want to list first file's
CLIENT_NAME

PLS help.

 

[Guest]

Your question is quite involved which may be why no answers yet. Perhaps if
I point out the issues i see, it might help you work through the problem or
clarify your question.

Issues:

You are looking for a query that combines two db files. - not sure this is
directly possible- may need to combine the tables into one file then query.
Typically one would have two tables in teh same database file and this type
of query is straight forward to sort and dump into excel - see the
Access-query newsgroup.

comming from .dbf instead of .dbm - might need to check SQL syntax for
establishing the connection and other issues try the access, programing
newsgroup.

Also it maters how many records and how ofter this needs to be done. If
small and one shot there could be workarounds that are easier to suggest.

In short you may need to break your problem into smaller pieces and post on
the access newsgroup.

Good luck

 

[Guest]

Hi sajay

As "Vacations Over" points out a little more info is needed.
Are you refering to info in two tables or two DB files.?
Either can be queried.
What BD are you trying to query.

Good Luck
TK

 

[michelxld]

Hello

you may try the macro to join the .dbf bases


Sub piloterDBase_jointureBases()
'activate Microsoft ActiveX Data Objects x.x Library
Dim Cn As ADODB.Connection
Dim Rs As ADODB.Recordset
Dim Chemin As String, Cible As String

'the dbf bases are supposed in the same directory
'C:\Documents and Settings\michel\dossier\Base1.dbf
'C:\Documents and Settings\michel\dossier\Base2.dbf
Chemin = "C:\Documents and Settings\michel\dossier"

Set Cn = New ADODB.Connection
Cn.Open _
"Driver={Microsoft dBASE Driver (*.dbf)};DriverID=277;Dbq=" & _
Chemin & ";"

'Base1= first file name(.dbf) has the following fields
CLIENT_ID,CLIENT_NAME
'Base2=Next file name(.dbf) has following fields
CLIENT_ID,BILL_DATE,BILL_AMOUNT
Cible = "SELECT Base1.CLIENT_NAME , Base2.BILL_DATE , Base2.BILL_AMOUNT
" & _
" FROM Base1 INNER JOIN Base2 ON Base1.CLIENT_ID=Base2.CLIENT_ID"

Set Rs = New ADODB.Recordset

With Rs
..ActiveConnection = Cn
..Open Cible, , adOpenStatic, adLockOptimistic, adCmdText
End With

If Rs.EOF Then
MsgBox "no records"
Rs.Close
Cn.Close
Exit Sub
End If

Range("A1").CopyFromRecordset Rs
Rs.Close
Cn.Close
End Sub



Regards ,
michel