Jet,MSDE,SQL Server and Providors

[gray]

Hi

Access 2002/2007 - please see attached

I've been reading up on connection strings, providors etc as I want my form Rs's to be ADO. At present they are not linked tables but 'local', I do, however, want to split them sooner of later. I'd like it to be backwards compatible with Acc2002 if possible.

At the moment, I keep getting "Data provider could not be initialized" esp when applying an ORDERBY.

I read advice about using ADOs which appears to be dependent upon which Db and connection etc one is using ... the question is... which one am I using??

I thought that Jet was dead and that Access used MSDE which itself is a cut-down version of SQL Server?

I've looked in the Access options screens but nothing leaps out... how do I know which type of Db and connection I'm presently using please? I know it's not SQL Server that's for sure cos' it ain't installed. I can't see MSDE in my WinXP task list but that's no real help anyway.

Thanks for any advice..

 

[Galaxiom]

JET is a database engine that is part of Windows. It was used by Access prior to 2007 when it was substituted with ACE. Functionally from the perspective of Access, the two are virtually equivalent. Access uses them natively and transparently.

Both local and split databases use them. For small databases (< 2GB) with relatively few (depends on what they are doing) users not having rigourous security and access control requirements this topology is perfectly adequate.

Otherwise use a database server. SQL Server Express (previously MSDE) is free. The 2008 version will support 10GB databases but uses limited memory and only one processor. For many simultaneous users employ the full version. Various complex licencing options are available, all of the them quite expensive.

There are other free alternative database servers such as MySQL and Postgre.

The easiest way to use a database server with Access is to link the tables in much the same way Access links to another Access database except it uses a DSN and ODBC. This does not use the strengths of the server, but it is a start. Then get into DSNless connections with the connection strings.

Using ADO recordsets is a considerable step and you should probably first gain experience with garden variety linked tables. BTW It is imperative you split your database before allowing multiple simultaneous user access and there are good reasons to split even for a single user.

 

[gray]

Hi

Great... thanks for such a comprehensive answer... For now I shall assume use of Jet/ACE when reading the ADO guidelines. I expect no more than a handful of users.

Incidentally, I went for ADO because I wanted to use SQL Transactions (BeginTrans etc) and I seemed to be getting non-updateable Rs prompts using that with DAO (when employing even a simple Join in the form's source). Moving to ADO seems to have got over that and armed with your advice I hope to lose the Data Providor issue.

Much appreciated...