Deciding the Appropriate connection type to use; Please advice.

[pedie]

Hi, I found different connection type....maybe more exist...
How do i decide which one I need to use?
And which can be faster as compared to the other in connecting to other database.

Please advice.

[/FONT]
[FONT=Courier New]Provider=Microsoft.Jet.OLEDB.4.[/FONT]
[FONT=Courier New]Driver={Microsoft Access Driver (*.mdb)};[/FONT]
[FONT=Courier New]DBEngine.Workspaces(0)
[/FONT]
[FONT=Courier New]

 

[xenou]

When working in Access with Access, stick with DAO - simplest and best.

When working with Access and other media (Excel, text files, other databases), you probably won't find much difference - just find something that works. After a while you'll see some different examples, try them out, and finally settle into whatever feels right to you.

 

[dk]

Pedie

You can use ADO to connect to many different data sources e.g. http://www.carlprothman.net/Default.aspx?tabid=81

If you are already using ADO I would stick with it rather than converting to DAO. DAO is not going to be developed going forward and apparently will not be available in Windows 64 bit - this provides some useful information: http://msdn.microsoft.com/en-us/library/ms810810.aspx

When connecting to an Access database using ADO you're better off using the Jet provider rather than the Microsoft Access Driver that you mentioned - read this for an explanation: http://msdn.microsoft.com/en-us/library/aa141406(v=office.10).aspx

HTH
DK

 

[xenou]

Thanks for chiming in dk - especially since I may be wrong!

Though I generally consider ACE to be the successor of JET (so perhaps I should say DAO/JET/ACE whatever that means). DAO is still my preference when working with Access in Access and it seems to be alive and kicking despite many rumors of its death. As far as I know, ACE is backwards compatible with JET so it seems we're still on good ground there going forward.

ξ

 

[Norie]

Pedie

Why not just find/use one that works for you and you are comfortable using?

 

[dk]

Can you use still use Jet 4.0 to connect to an Access 2007/2010 (accdb) database?

I always thought you couldn't.

If you want to connect to an accdb file you need to use the ACE provider e.g.

Sub conntest()
Dim conn As ADODB.Connection

conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\some folder\somefile.accdb;Persist Security Info=False;"


End Sub

 

[Norie]

Yes, that's what I thought.

I'm sure I've had to use both ACE and JET before when working between different applications.

Was hoping I might of missed sonething.

PS Don't know what happened to that part of the post I edited to add the comment for pedie.

I swear my keyboard must have nicked it.

 

[pedie]

Thanks everyone. Thanks for the advice. Well the thing is that I'm new to access and I have seen a lot of examples from different sites and all this information sometime is confusing espcially the term which are new to me like

[/FONT]
[FONT=Courier New]rst.CursorType = adOpenStatic [/FONT]
[FONT=Courier New]rst.Open "TableName",Connection, , adLockOptimistic/adLockPessimistic/...[/FONT]
[FONT=Courier New]

etc....

Currently i use somthing like this...

[/FONT]
[FONT=Courier New]Set CNXN = New ADODB.Connection
strCNXN = "Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=mydb;Uid=;Pwd=;"
CNXN.Open strCNXN[/FONT]
[FONT=Courier New]

'm trying the JET Connection thing now

 

[Norie]

Pedie

This isn't really Access stuff, not for someone just starting with it anyway.