mdb to adp

[Ali, Sifte]

I've converted my Access Database to Access Project (mdb to adp) but problem
is this that some queries and sql written in code is not working. Please
advice me.

 

[Larry Linson]

I've converted my Access Database to Access
Project (mdb to adp) but problem is this that
some queries and sql written in code is not
working. Please advice me.

To what purpose did you convert? The only reason I know to make such a
conversion was the assumption that ADP worked better with linked MS SQL
Server tables. That seems not to be the case, as the Access development team
now recommends MDB-DAO-Jet-ODBC-MSSQLServer as the method of choice over
ADP-ADO-ADODB-MSSQLServer.

The queries and SQL use slightly different notation because they are are
using OLEDB -- it's more like the MS SQL Server "dialect" of SQL. (Actually,
I think it IS the SQL Server dialect, but there may be some minor
differences.) Did you make a conversion thinking that there would not have
to be any manual modifications?

Larry Linson
Microsoft

 

[Ali, Sifte]

Using DAO in mdb file is slower than OLEDB/ADO thats why i choose adp.
actually i have a project which is supposed to be run over WAN/VPN,
nation-wide. For this purpose i prefere to use DB Server rather than file
sharing topology. I have tried both by making DSN file and creating a link
using OLEDB/ADODB and I found adp is a faster and better option as using DSN
file on mdb found slower. Please advice further.

 

[Larry Linson]

Using DAO in mdb file is slower than OLEDB/ADO
thats why i choose adp. actually i have a project which
is supposed to be run over WAN/VPN, nation-wide.
For this purpose i prefere to use DB Server rather than file
sharing topology.

I'm sorry if I misled you -- I certainly did not suggest using a split
Access-Jet database in that situation. I suggested using MDB - DAO - ODBC -
MS SQL Server, which with good design for the client-server environment, has
served me well with several different Server databases. I could see no
worthwhile speed improvments in the ADP environment in which I did some
work.

I have tried both by making DSN file and creating a link
using OLEDB/ADODB and I found adp is a faster and
better option as using DSN file on mdb found slower.
Please advice further.

When you say "by making DSN file", I assume you mean "using ODBC drivers" to
compare speed against the OLE DB approach using ADO. If you have done an
extensive comparison and found it sufficiently faster to be worth the bother
in your environment, who am I to suggest you do otherwise?

To the best of my knowledge, I answered your original question. For details,
in Access 2003 in the Database Window, go to Help, choose Microsoft Access
Help, search for "Jet SQL", then in the list of topics, choose the article
entitled "Comparison of Microsoft Jet SQL and ANSI SQL" and then also read
the article entitled "Access projects and data access pages now use ANSI
SQL-92 Syntax."

Larry Linson
Microsoft Access MVP