conversion from DAO

[LukasMalik]

Hi all,

I have very very old program in MS Access (originaly 2.0, somehow converted into 95). Data are stored together with business logic in .mdb file. It does not works more, so I was asked to convert into 2003 and deploy database in MS SQL server.
My first step was to register DAO dao2535.tlb, and link tables using ODBC connector. Well, it works, but some functions throw errors <Runtime error 3219> which seems to be SQL syntaxt conflict.
So, project is able to be used for some months, but finally I have to make some serious solution. What will you advice me? Instead of old and obsolete DAO methods, datasets use ADO or OLEDB or ODBC?

Thanks for any answer

[PianoMan64]

If you're going to connect to a MS SQL server as your data backend, the first thing that I would suggest to you is to upsize the backend database tables to MS SQL.

After getting that completed. the easiest way to connect to a MS SQL Tables would be to use ADODB to connect to all tables, that way you would limit the amount of syntax that you're going to have to change between DAO and ADODB.

Since DAO is still a very strong and viable tool in MS Access 2003, I would suggest you stay with that as much as you can, but there are some options that were used in Access 2.0 that are no longer supported in 2003.

If you can give me code snippits of the area that you're having problems with, I can definitly assist you with getting those converted to ADODB.

Hope that helps,

Joe P.

[LukasMalik]

If you're going to connect to a MS SQL server as your data backend, the first thing that I would suggest to you is to upsize the backend database tables to MS SQL.

After getting that completed. the easiest way to connect to a MS SQL Tables would be to use ADODB to connect to all tables, that way you would limit the amount of syntax that you're going to have to change between DAO and ADODB.

Since DAO is still a very strong and viable tool in MS Access 2003, I would suggest you stay with that as much as you can, but there are some options that were used in Access 2.0 that are no longer supported in 2003.

If you can give me code snippits of the area that you're having problems with, I can definitly assist you with getting those converted to ADODB.

Hope that helps,

Joe P.

I tried to convert mdb format to adp but it has failed. Previous programmer has solved it with ODBC conection. He deleted all tables in mdb and had linked new from SQL server through ODBC. Is this what you ment in first step? Well, I am little confused, I am relatively well experienced with .NET and C# stuff but this is something totaly new. In .NET with ADO I connect directly to server> database > (table|view|sto red p)

Sorry for asking dumb questions, I am little lost with this

[PianoMan64]

The first step that I was speaking about is this, There is a wizard that comes with version 2000 or higher of access that will allow you to convert the MDB file into an ADP file and also transfer all your data to MS SQL. This is what I'm speaking of. If you're having problems converting the file, my suggestion to you would be to create a new MDB file with nothing in it at all. Then import all your tables only into that new MDB file, then convert that using the Upsize tool that comes with MS Access to convert all your data to MS SQL.

That should do the trick. Then you can import all your forms and modules and Macros into your new ADP file.

Hope that helps,

Joe P.

[Jim Doherty]

I tried to convert mdb format to adp but it has failed. Previous programmer has solved it with ODBC conection. He deleted all tables in mdb and had linked new from SQL server through ODBC. Is this what you ment in first step? Well, I am little confused, I am relatively well experienced with .NET and C# stuff but this is something totaly new. In .NET with ADO I connect directly to server> database > (table|view|sto red p)

Sorry for asking dumb questions, I am little lost with this

You could create a new blank ADP file and import all your tables via that method that 'serves' to send your tables to the SQL Server in much the same way as the upsizing wizard.

ADP files 'are' different from MDB files speaking directly with the SQL Server via udl connection....b ottom line is expect to deal with a number of conversion issues if you use the ADP file as your frontend interface.

I personally prefer the ADP interface so am quite happy to rebuild forms (copy paste existing controls and amend existing code where necessary) and base them on views or stored procedures the gain is ultimately worth it (having prebuilt ability in forms to deal with parametisation of stored procedures and a visualisation of server objects in the Access interface amongst other things makes life so much easier if nothing else).

Jim :)