Joining FoxPro Tables in Microsoft Access via OLEDB

[TerryJayFoster]

I have an external application that writes to independent tables in
FoxPro format. I am able to access the tables just fine with an ADO
connection and add, edit and update.

My problem is that I need to join the results from two of the tables.
There is one table that has a list of locations, indexed by a key, and
another table that has events with the location index.

I need to join these two tables and present the results on a report.
I would prefer not to use ODBC, if possible.

Any way to get this done?

 

[Jeff Boyce]

Are you saying that you can see the tables in your Access Tables tab?

Have you tried creating a query and specifying those tables? You can do the
joining in the query.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[TerryJayFoster]

Jeff,

Thanks for replying. If I was seeing the tables in the tables tab
that would mean I would have either:

1. Imported the tables. Not desireable at all since I need to leave
them where the other application can get to them.

or

2. Use ODBC to link them. This is not the behavior I want either
since I want this code to be exportable to ASP eventually.

There is another way to access the tables. In Access I write code
like this to create ab OLEDB connection and use the returned
recordsource for my form:

Private Sub Form_Open(Cancel As Integer)
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

'Create a new ADO Connection object
Set cn = New ADODB.Connection

cn.Provider = "vfpoledb.1;Data Source="C:\PathToDBFTable
\";SourceType=DBF"
cn.Open

'Create an instance of the ADO Recordset class, and
'set its properties
Set rs = New ADODB.Recordset
With rs
Set .ActiveConnection = cn
.LockType = adLockOptimistic
.CursorType = adOpenKeyset
.Open "SELECT * FROM [MyDBFTable]", , , , adCmdText
Set Me.Recordset = rs
.Close
End With

cn.close
Set rs = Nothing
Set cn = Nothing

End Sub

With the above connection I have no problems. My problem is that I
need to connect to another table and join it with the first and use
the results for my form. I don't need it to be updateable, just
displayed.

Can anyone help?

Terry

 

[Jeff Boyce]

I'll step back ... no experience in this arena.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,

Thanks for replying. If I was seeing the tables in the tables tab
that would mean I would have either:

1. Imported the tables. Not desireable at all since I need to leave
them where the other application can get to them.

or

2. Use ODBC to link them. This is not the behavior I want either
since I want this code to be exportable to ASP eventually.

There is another way to access the tables. In Access I write code
like this to create ab OLEDB connection and use the returned
recordsource for my form:

Private Sub Form_Open(Cancel As Integer)
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

'Create a new ADO Connection object
Set cn = New ADODB.Connection

cn.Provider = "vfpoledb.1;Data Source="C:\PathToDBFTable
\";SourceType=DBF"
cn.Open

'Create an instance of the ADO Recordset class, and
'set its properties
Set rs = New ADODB.Recordset
With rs
Set .ActiveConnection = cn
.LockType = adLockOptimistic
.CursorType = adOpenKeyset
.Open "SELECT * FROM [MyDBFTable]", , , , adCmdText
Set Me.Recordset = rs
.Close
End With

cn.close
Set rs = Nothing
Set cn = Nothing

End Sub

With the above connection I have no problems. My problem is that I
need to connect to another table and join it with the first and use
the results for my form. I don't need it to be updateable, just
displayed.

Can anyone help?

Terry

Are you saying that you can see the tables in yourAccessTables tab?

Have you tried creating a query and specifying those tables? You can do
the
joining in the query.

Regards

Jeff BoyceMicrosoftOffice/AccessMVP









- Show quoted text -

 

[TerryJayFoster]

BTW: I had hoped to use the SHAPE command to implement this.
(something tike:

SHAPE {SELECT * FROM Table1} APPEND ({SELECT * FROM Table2} RELATE
placeid TO placeid)

but all I get is an "Unrecognized Verb" error.

Jeff,

Thanks for replying. If I was seeing the tables in the tables tab
that would mean I would have either:

1. Imported the tables. Not desireable at all since I need to leave
them where the other application can get to them.

or

2. Use ODBC to link them. This is not the behavior I want either
since I want this code to be exportable to ASP eventually.

There is another way toaccessthe tables. InAccessI write code
like this to create ab OLEDB connection and use the returned
recordsource for my form:

Private Sub Form_Open(Cancel As Integer)
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

'Create a new ADO Connection object
Set cn = New ADODB.Connection

cn.Provider = "vfpoledb.1;Data Source="C:\PathToDBFTable
\";SourceType=DBF"
cn.Open

'Create an instance of the ADO Recordset class, and
'set its properties
Set rs = New ADODB.Recordset
With rs
Set .ActiveConnection = cn
.LockType = adLockOptimistic
.CursorType = adOpenKeyset
.Open "SELECT * FROM [MyDBFTable]", , , , adCmdText
Set Me.Recordset = rs
.Close
End With

cn.close
Set rs = Nothing
Set cn = Nothing

End Sub

With the above connection I have no problems. My problem is that I
need to connect to another table and join it with the first and use
the results for my form. I don't need it to be updateable, just
displayed.

Can anyone help?

Terry

Are you saying that you can see the tables in yourAccessTables tab?

Have you tried creating a query and specifying those tables? You can do the
joining in the query.

Jeff BoyceMicrosoftOffice/AccessMVP

- Show quoted text -- Hide quoted text -

- Show quoted text -

 

[TerryJayFoster]

OK, in my case the answer was relatively easy. I can't believe it took
me so long to figure it out:

strSQL = "SELECT p.name, e.* FROM [Table1] AS e LEFT JOIN [Table2] AS
p ON e.placeid = p.placeid;"

For some reason this did not work until I put the alias in for the
table names. ???

This works as long as the two tables are in the same directory. I
would be curious as to how one would handle this if they were in
different directories (the connection string would be different).
Doesn't matter now. I'm over this road block!

Terry

BTW: I had hoped to use the SHAPE command to implement this.
(something tike:

SHAPE {SELECT * FROM Table1} APPEND ({SELECT * FROM Table2} RELATE
placeid TO placeid)

but all I get is an "Unrecognized Verb" error.

Jeff,

Thanks for replying. If I was seeing the tables in the tables tab
that would mean I would have either:

1. Imported the tables. Not desireable at all since I need to leave
them where the other application can get to them.

2. Use ODBC to link them. This is not the behavior I want either
since I want this code to be exportable to ASP eventually.

There is another way toaccessthe tables. InAccessI write code
like this to create ab OLEDB connection and use the returned
recordsource for my form:

Private Sub Form_Open(Cancel As Integer)
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

'Create a new ADO Connection object
Set cn = New ADODB.Connection

cn.Provider = "vfpoledb.1;Data Source="C:\PathToDBFTable
\";SourceType=DBF"
cn.Open

'Create an instance of the ADO Recordset class, and
'set its properties
Set rs = New ADODB.Recordset
With rs
Set .ActiveConnection = cn
.LockType = adLockOptimistic
.CursorType = adOpenKeyset
.Open "SELECT * FROM [MyDBFTable]", , , , adCmdText
Set Me.Recordset = rs
.Close
End With

cn.close
Set rs = Nothing
Set cn = Nothing

End Sub

With the above connection I have no problems. My problem is that I
need to connect to another table and join it with the first and use
the results for my form. I don't need it to be updateable, just
displayed.

Can anyone help?

Terry

- Show quoted text -- Hide quoted text -

- Show quoted text -

 

[Guest]

In FoxPro, the directory is the database. If you want to use
tables in two different databases, try something like this:
....FROM [database].[tablename] ...

I don't know what exactly you would put for [database]
-- I don't use either ADO nor FoxPro much.

(david)

OK, in my case the answer was relatively easy. I can't believe it took
me so long to figure it out:

strSQL = "SELECT p.name, e.* FROM [Table1] AS e LEFT JOIN [Table2] AS
p ON e.placeid = p.placeid;"

For some reason this did not work until I put the alias in for the
table names. ???

This works as long as the two tables are in the same directory. I
would be curious as to how one would handle this if they were in
different directories (the connection string would be different).
Doesn't matter now. I'm over this road block!

Terry

BTW: I had hoped to use the SHAPE command to implement this.
(something tike:

SHAPE {SELECT * FROM Table1} APPEND ({SELECT * FROM Table2} RELATE
placeid TO placeid)

but all I get is an "Unrecognized Verb" error.

Jeff,

Thanks for replying. If I was seeing the tables in the tables tab
that would mean I would have either:

1. Imported the tables. Not desireable at all since I need to leave
them where the other application can get to them.

2. Use ODBC to link them. This is not the behavior I want either
since I want this code to be exportable to ASP eventually.

There is another way toaccessthe tables. InAccessI write code
like this to create ab OLEDB connection and use the returned
recordsource for my form:

Private Sub Form_Open(Cancel As Integer)
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

'Create a new ADO Connection object
Set cn = New ADODB.Connection

cn.Provider = "vfpoledb.1;Data Source="C:\PathToDBFTable
\";SourceType=DBF"
cn.Open

'Create an instance of the ADO Recordset class, and
'set its properties
Set rs = New ADODB.Recordset
With rs
Set .ActiveConnection = cn
.LockType = adLockOptimistic
.CursorType = adOpenKeyset
.Open "SELECT * FROM [MyDBFTable]", , , , adCmdText
Set Me.Recordset = rs
.Close
End With

cn.close
Set rs = Nothing
Set cn = Nothing

End Sub

With the above connection I have no problems. My problem is that I
need to connect to another table and join it with the first and use
the results for my form. I don't need it to be updateable, just
displayed.

Can anyone help?

Terry
On Jul 9, 3:11 pm, "Jeff Boyce" <[email protected]> wrote:

Are you saying that you can see the tables in yourAccessTables tab?

Have you tried creating a query and specifying those tables? You can do the
joining in the query.

Jeff BoyceMicrosoftOffice/AccessMVP

I have an external application that writes to independent tables in
FoxPro format. I am able toaccessthe tables just fine with an ADO
connection and add, edit and update.

My problem is that I need to join the results from two of the tables.
There is one table that has a list of locations, indexed by a key, and
another table that has events with the location index.

I need to join these two tables and present the results on a report.
I would prefer not to use ODBC, if possible.

Any way to get this done?- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -