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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

heer2351Commented:
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 :)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
heer2351Commented:
He Alan since when do you have access to my favorites :)
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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 :)
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
Alan WarrenApplications DeveloperCommented:
LOL

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

:)
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.

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 :)
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.
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"
Alan WarrenApplications DeveloperCommented:
thats good news, you have a connection then!

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

Alan :)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.