How to improve compatibility with MSSQL and ACCESS

[Alondra]

Hi all,

We all know that ASP among the most commonly used is the ACCESS and the two MSSQL databases. Many programs want to develop into two databases can be used to model, but because of the two databases of some SQL statements were inconsistent, resulting in a lot of unnecessary programs in two versions, that is, ACCESS, and MSSQL . So, I want to know that how in the end there is a database program compatibility and get to function efficiently. Please suggest. Thanks in advance.

[Praetor]

You have to use control method in a parameter, the operation of the program to know that you are now what is the database.

Const SystemDatabaseType123 = "ACCESS" 'system, database type, "SQL" as the MSSQL 2005 Database, "ACCESS" database for MS ACCESS 2003.

This is the compatibility of a basis to begin the whole process a database discriminant. Therefore, it is very necessary to set such a parameter.

[Katty]

The database connection method and commonly used functions of the distinction between is helpful to improve compatibility with MSSQL and ACCESS. The differences between the database connection after the MSSQL and ACCESS will also be used for variable assignment statements. The benefits of doing so is that behind the use of relevant content when a variable can be used directly call the method, rather than the need for a database type of judgments.

[kelfro]

Many people write MSSQL database is like to use conn.execute statement, instead of using the open method. Although the speed at which a lot of time conn.execute than open a way as to speed a bit. But this is not to generalize. For example, in a process to determine whether there is already among the algorithms. If you have already used the open had the judgments BOF or EOF, then you should use to judge conn.execute, then speed, certainly not as good as a direct change in the OPEN good. Moreover, in the code which open the way compatibility is far higher than conn.execute. For example, a function of time now () to use. Check and reply.

[Zecho]

You can use conn.execute. But I was not to generalize. For example, if it is a less used, is not a frequent reading process. I would choose to use the OPEN method instead of using the conn.execute way. Although the speed of this process will be reduced. But this does not affect my use, so I chose to OPEN mode. The reason is simple, that is, the compatibility choice. I suggest you to also prefer same choice. Best of luck.

[Reegan]

When you encounter some compatibility can not be resolved, or the like conn.execute and OPEN when the pursuit of way. you can use the previous SystemDatabaseType parameter settings to determine, and then the appropriate use of different code.Such as:

If SystemDatabaseType = "MSSQL" Then
'MSSQL database using the code
Else
'ACCESS database using the code
End If

This will ensure program efficiency and stability.