converting mdb to access project

[mazza]

It is the first time i am attempting to create an access project file
I have an existing mdb application I want to move to a SQL back end.

I looked into the possibilities, hwr the problem is I have many subs or functions using the VBA sql syntext and also a lot of DAO references to open record sets or tables etc.

If I understand it correctly I need to convert all DAO's to ADO's etc. As I am not too familiar with ADO it seems a bit of a nightmare.

Is there an easy way to convert an MDB, with dao usage etc into a project?

the alternative is just to link tables to from a mdb front end to a sql database with some minor twaeking of some of the codes.

Does anybody with experience give me some advise, tips pro and cons?

 

[Dennisk]

I use both .mdb and .adp files.
pros. .ADP: for large datasets you really need SQL and .adp as this is true Client server technology ie processing is performed on the SQL server and only the result set is transferred back to the FE. Ability to use views and stored procedures. One internal connection to connect FE to BE.

Pros: .MDB ease of use. Can reuse existing queries.

Cons: .ADP required ADO and a different approach to forms design especially paramter driven queries.

Cons: .MDB can be very slow unless pass-through queries are used and any VBA functions are converted to T-SQL. A primary key is required for all tables so the ODBC driver can pass back updates to the correct row..