Get Access to Paradox Data File With Various File Name?

[jaykchan]

I need to get access to a great number of Paradox data files. The
problem is that the Paradox data files are constantly changing. This
means I cannot use interactive tool to add a static link to the Paradox
data files or to import the Paradox data files. I need a way to
programmatically connect to the Paradox data files dynamically.

I believe I need to open a ODBC connection to Paradox data files with
some kind of connection string. I have added a ODBC connection to the
folder where the Paradox data files are. But I cannot figure out what
the connection string should be.

Please help.

Thanks.

Jay Chan

 

[Arvin Meyer]

Using the Link functionality is all you need for a live connection to a
Paradox database.

Choose File ... Get External Data ... Link then change the file type to
Paradox and connect and link the file.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access Downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[jaykchan]

Using the Link functionality is all you need for a live connection to a

Paradox database.

Choose File ... Get External Data ... Link then change the file type to
Paradox and connect and link the file.

Unfortunately, this is not what I am asking for. What you have
suggested works fine with linking to a Paradox data file that I know
the file name and location ahead of time (then I can interactively link
to it). But this doesn't work with Paradox data file that I don't know
the file name ahead of time. As a matter of fact, I don't know the
exact folder that the files are stored; I only know the parent folder
where all the sub-folders and data files are being stored. This is like
this (the .DB file is a Paradox data file):

F:\Jobs\Job001\Job001.DB
\Job002\Job002.DB
\Job003\Job003.DB

 

[jaykchan]

I finally find the correct connection string for the Paradox data
files:

sFullPathJobFolder = "F:\Jobs\Job0001"
sConnStr = "Driver={Microsoft Paradox Driver (*.db )};" & _
"DriverId=538;" & _
"Fil=Paradox 5.X;" & _
"DefaultDir=" & sFullPathJobFolder & ";" & _
"Dbq=" & sFullPathJobFolder & ";" & _
"CollatingSequence=ASCII"

Shown above, I can change the path to dynamically change the database
connection to a different folder.

Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
Dim sTableName As String
sTableName = "Job0001"
rst.Open "select JobName from " & sTableName, _
m_cnnQeJob, adOpenForwardOnly

Shown above, I can change the table name dynamically just by changing
the variable.

The thing that cause me a lot of trouble is the fact that the Paradox
data drive doesn't like to see a table name (meaning the Paradox file
name) that has a '-' in it. This took me a lot of trial and error to
figure this out.

Now, I just need to fix that little problem with some odd field names
in Paradox and I will be all set.

Thanks.

Jay Chan

 

[Arvin Meyer [MVP]]

I don't think that an ODBC connection using a DSN will be any different,
other than it will give you a named connection. The connection string can be
ascertained by opening the table in design view (ignore the warning about
not being able to make changes) Then right-click on the Titlebar and choose
properties, or choose it from the View menu. The connection string is in the
first property (The Description property)
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[jaykchan]

The connection string can be ascertained by opening the

table in design view (ignore the warning about not being
able to make changes) Then right-click on the Titlebar
and choose properties, or choose it from the View menu.
The connection string is in the first property (The
Description property)

Thanks for the tips. I didn't know this easy way to get the connection
string.

Jay Chan