Queries missing in ADP and SQL after upsizing

[Donna Brooks]

Hi,
I am a rookie where SQL is concerned and I'm trying to create an SQL backend
with an Access front end. So I used the upsizing wizard to convert my
database to an adp and link up all the tables in SQL. However, quite a few of
my queries are not showing up in SQL or the ADP. How can I add these
additional queries/views to SQL? Any help is appreciated.

 

[Paul Shapiro]

Access query syntax is pretty far from the SQL standard. For example, Access
allows VBA functions in the sql, which SQL Server can't process because it
doesn't know those functions. Access has non-standard aggregate functions
like first() and last(), which are not available in SQL Server. Etc.

The last I knew (a few years back) the upsizing wizard ignores queries if
the sql is not legitimate SQL Server syntax. The only option is to re-create
the queries in SQL Server, using the Access sql as a guide.

You should look for some Microsoft guidance on the query upsizing. One
pitfall I remember is that True/False are -1/0 in Access (and the Jet
database), but are 1/0 in SQL Server. If you used true and false in Access,
the query might compile fine in SQL Server, but give the wrong result.

 

[Donna Brooks]

Thanks Paul. I found my queries upsized as User-Defined Functions in SQL, any
that had ORDER BY or parameters. I need to be able to pull a value from the
active form, which in Access would be [form]![cmbModel]... I tried pasting
the sql view into a new query in sql and its giving me a syntax error.. Any
suggestions?

 

[Sylvain Lafontaine]

Instead of upsizing your mdb file to ADP, you should try first to upsize
your MDB file to another MDB file this time but with ODBC Linked Tables.
It's much more easier to work with a MDB file and ODBC Linked Tables than
with an ADP project; especially if you're a rookie about SQL-Server.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[Paul Shapiro]

Access has that nice feature of integrating the query engine with the forms,
but SQL Server can't do it. You can create a user-defined function or a
stored procedure with a parameter, and then execute the stored procedure via
VBA code, passing it the parameter value. I'm not sure but I seem to
remember a form may automatically pass the parameter value if a control name
exactly matches the parameter name.

The SQL standards do not allow sorting in Views, which is what an Access
Select query represents. Access does allow the sort, but depending on which
SQL Server version you're using, SQL Server does not. User-defined functions
and stored procedures both support parameters and sorting.

Thanks Paul. I found my queries upsized as User-Defined Functions in SQL,
any
that had ORDER BY or parameters. I need to be able to pull a value from
the
active form, which in Access would be [form]![cmbModel]... I tried pasting
the sql view into a new query in sql and its giving me a syntax error..
Any
suggestions?

Access query syntax is pretty far from the SQL standard. For example,
Access
allows VBA functions in the sql, which SQL Server can't process because
it
doesn't know those functions. Access has non-standard aggregate functions
like first() and last(), which are not available in SQL Server. Etc.

The last I knew (a few years back) the upsizing wizard ignores queries if
the sql is not legitimate SQL Server syntax. The only option is to
re-create
the queries in SQL Server, using the Access sql as a guide.

You should look for some Microsoft guidance on the query upsizing. One
pitfall I remember is that True/False are -1/0 in Access (and the Jet
database), but are 1/0 in SQL Server. If you used true and false in
Access,
the query might compile fine in SQL Server, but give the wrong result.