Access 2002 connection to Paradox using ADO

Okay, I am trying to connect to a Paradox table with Access 2002 and would like to use ADO to do it, but I am not to familiar with ADO and can not find many sources of very useful information on ADO.  

First of all the folder that I am using is: P:\ForACC and the folder and file that I am trying to connect to is: P:\Fleet\Fleet.db  and another table I need to connect to is:  P:\Fleet\lert.db.  Can someone show me some sample code for connecting, copying records from these two tables, and then closing the connection?  The records I need are from the Fleet table: VIN, Agency, Year, and lert and from the lert they are: Index and lert.  

These two tables are on a Novel Network.  Is it possible for me to create a user with access rights to all of the above folders and then have the ado connection use the username and password to gain access to the tables and records that I need brought in?  Thanks for the help...
cpophamAsked:
Who is Participating?
 
Alan WarrenApplications DeveloperCommented:
Hi cpopham


http://www.able-consulting.com/MDAC/ADO/Connection/ODBC_DSNLess.htm#ODBCDriverForParadox

Set up your connection string as depicted in the link above.


Private Sub Somesub()
'Reference: Microsoft ActiveX data objects 2.xx Library

dim oConn as adodbb.connection
set oconn = new adodb.connection
dim rs as adodb.recordset
set rs = new adodb.recordset

' Copied from the link above
oConn.Open "Driver={Microsoft Paradox Driver (*.db )};" & _
           "DriverID=538;" & _
           "Fil=Paradox 5.X;" & _
           "DefaultDir=c:\dbpath\;" & _
           "Dbq=c:\dbpath\;" & _
           "CollatingSequence=ASCII"

oConn.CursorLocation = adUseClient

dim sql as string
sql = "select yourfield from yourtable"
Rs.CursorLocation = adUseClient
Rs.Open sql, oConn, adOpenStatic, adLockOptimistic, adCmdText

End Sub

Alan :)
0
 
heer2351Commented:
0
 
heer2351Commented:
He Alan since when do you have access to my favorites :)
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Alan WarrenApplications DeveloperCommented:
Hi Hans,

Able-consulting are good resource, in my favourites too.
Also number 1 find in google when searching for connection strings

I think originally it may have been you who put me onto able-consulting.

Regards

Alan :)
0
 
heer2351Commented:
Alan,

I was just smiling when I saw that you posted exactly the same link, could not resist to post a little joke.

However you took the time to hack up an example which is probably required when I read the question.

OT: Have you already found a store to buy a Sage costume, I might also be needing it in some time ....

Hans
0
 
Alan WarrenApplications DeveloperCommented:
LOL

No just budgeting for it at the moment...Still don't know if I need a horse too!

:)
0
 
heer2351Commented:
I hope the store accepts EE points for payment if we also need a horse.

However I think a beard and grey hair will suffice.

\Sage\, n. A wise man; a man of gravity and wisdom; especially, a man venerable for years, and of sound judgment and prudence; a grave philosopher.

0
 
Alan WarrenApplications DeveloperCommented:
I reckon EE should provide them, required resources to fulfill our duty and all that stuff. I got both beard and long grey hair naturally, bit of an old hippie from the 70's.

Alan :)
0
 
heer2351Commented:
So I guess you are a real Sage!

No beard, no grey hair (well a few), I guess I have to stay an apprentice for a while.
0
 
cpophamAuthor Commented:
Thanks guys,

When I ran it, the compiler did not like the statement:

rs.Open sql, oConn, adOpenStatic, adLockOptimistic, adCmdText

It gave me a runtime error and said invalid sql statement.  Expected 'INSERT', 'DELETE', blah, blah.

Also, if I put a network user ID and password in the code liek this will it work to let it basically sign on?

oConn.Open "Driver={Microsoft Paradox Driver (*.db )};" & _
           "DriverID=538;" & _
           "Fil=Paradox 5.X;" & _
           "DefaultDir=p:\fleet\;" & _
           "Dbq=p:\fleet\;" & _
           "Exclusive=False" & _
           "PARADOXNETPATH=P:\Pdata" & _
           "PARADOXUSERNAME=teamptr" & _
           "PWD=redrum101" & _
           "CollatingSequence=ASCII"
0
 
Alan WarrenApplications DeveloperCommented:
thats good news, you have a connection then!

What was the sql you contructed, can you post it here?

Alan :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.