Copy Query from MDB to ADP

[rogge]

I am trying to move queries in an Access database, MDB file, to an Access
Data Project, ADP.

I can get the information needed from the MDB, the query name and its SQL
string, easily. I am having difficulty creating a new View in the ADP using
VBA. When I run this code, an Object Not Set error occurs in the ADP at Set
qryTemp, this error does not happen in a MDB.

Set dbs = OpenDatabase(strDB)

For Each qry In dbs.QueryDefs

Set qryTmp = CurrentDb.CreateQueryDef(qry.Name, qry.SQL)

Next qry

 

[Tim Ferguson]

I can get the information needed from the MDB, the query name and its
SQL string, easily. I am having difficulty creating a new View in the
ADP using VBA.

The whole point of an ADP is that it's not a Jet/DAO kind of environment,
it's a SQL Server/ADO thing. Therefore, you can get the query out of the
mdb, but you'll have to do some conversion before adding it to the SQL
Server database. At the least it'll look like:

' this bit is easy as you already know
Set qdf = GetQDFFromOldDatabase(queryName) ' etc
jetSQL = qdf.SQL

' change wildcards, function calls, date formats, etc
adoSQL = ConvertToANSI(jetSQL)

' now set up the new thing; actually it's much harder to
' to decide whether a query is a view or a sp
If left(adoSQL,6)="SELECT" then
' assume it's a view
adoSQL = "CREATE VIEW " & queryName & " AS " & vbNewLine & _
adoSQL
CurrentProject.Connection.Execute adoSQL
' keep fingers crossed because I never worked out how to
' trap SQL errors in ADPs

Else
' we'll step over the thorny issue of parameters and return
' values etc etc
adoSQL = "CREATE PROCEDURE " & queryName & " AS " & vbNewLine & _
adoSQL
CurrentProject.Connection.Execute adoSQL

End If

When you think about it, Microsoft had huge amounts of time and
experience to throw at this problem and the mdb upsizer is still pretty,
well, rubbish. That's because it's a big problem with lots of intelligent
and informed decisions to be made, not (just for once) because their
programming team is pants.

The lesson is that moving from mdb to adp is not trivial: you have a lot
of work to do recoding by hand your table definitions (triggers and
relational constraints, for example) as well as the queries, as well as
changing a lot of your db procedures to take advantage of the new
environment (eg doing all the filtering, counting, sorting on the server
instead of the client etc etc)

Hope that helps


Tim F




When I run this code, an Object Not Set error occurs