G
Guest
The title on my previous post was misleading. Sorry.
Is there an easy way to "upsize" mdb QBE queries to SQL Server? Thanks.
Is there an easy way to "upsize" mdb QBE queries to SQL Server? Thanks.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
B
Baz
John Lane said:The title on my previous post was misleading. Sorry.
Is there an easy way to "upsize" mdb QBE queries to SQL Server? Thanks.
No. There are differences in SQL syntax that will need to be addressed.
Also, any use of parameters and Access functions would need to be looked at,
and may result in the need to substantially redesign things.
Of course, if you simply upsize the Access back-end database to SQL Server
using ODBC linked tables, then the Access queries will mostly continue to
run exacrtly as they are, although not necessarily very efficiently.
G
Guest
when i have a large number of queries in access that i need to rewrite in
sql; i use this method
a) push all the access qdf.sql into a table
b) add a column for the tsql version of that code.
c) use update queries in order to replace the semicolon-- or anyhting else
that you want to replace
d) loop through the tsql and try to create everything as a view first, and
then as a sproc (a lot of people have stacked queries in access and relying
on views gets it up-- but it frequently works best to remove stacked views
if you're having performance problems)
e) get some sort of coutn of how many queries are left to create
f) if you store the status for all this in a table; it's pretty
straightforward to join sysobjects to the objects that are complete and then
get a list of which objects haven't been created yet
g) if you dive into it; basically the conversion from access to sql is just
a series of replaces that you need to run on the accessql in order to
massage it into legit tsql
like previously where you used the phrase 'NOW()' you would swap in
'GETDATE()' instead
where you use the word date, replace the word 'DATE()' with
convert(datetime, (convert(int, getdate()-.5))
when you have updates on joins; you'll need to basically rewrite them one by
one
-aaron
sql; i use this method
a) push all the access qdf.sql into a table
b) add a column for the tsql version of that code.
c) use update queries in order to replace the semicolon-- or anyhting else
that you want to replace
d) loop through the tsql and try to create everything as a view first, and
then as a sproc (a lot of people have stacked queries in access and relying
on views gets it up-- but it frequently works best to remove stacked views
if you're having performance problems)
e) get some sort of coutn of how many queries are left to create
f) if you store the status for all this in a table; it's pretty
straightforward to join sysobjects to the objects that are complete and then
get a list of which objects haven't been created yet
g) if you dive into it; basically the conversion from access to sql is just
a series of replaces that you need to run on the accessql in order to
massage it into legit tsql
like previously where you used the phrase 'NOW()' you would swap in
'GETDATE()' instead
where you use the word date, replace the word 'DATE()' with
convert(datetime, (convert(int, getdate()-.5))
when you have updates on joins; you'll need to basically rewrite them one by
one
-aaron
B
Baz
when i have a large number of queries in access that i need to rewrite in
sql; i use this method
a) push all the access qdf.sql into a table
b) add a column for the tsql version of that code.
c) use update queries in order to replace the semicolon-- or anyhting else
that you want to replace
d) loop through the tsql and try to create everything as a view first, and
then as a sproc (a lot of people have stacked queries in access and relying
on views gets it up-- but it frequently works best to remove stacked views
if you're having performance problems)
e) get some sort of coutn of how many queries are left to create
f) if you store the status for all this in a table; it's pretty
straightforward to join sysobjects to the objects that are complete and then
get a list of which objects haven't been created yet
g) if you dive into it; basically the conversion from access to sql is just
a series of replaces that you need to run on the accessql in order to
massage it into legit tsql
like previously where you used the phrase 'NOW()' you would swap in
'GETDATE()' instead
where you use the word date, replace the word 'DATE()' with
convert(datetime, (convert(int, getdate()-.5))
when you have updates on joins; you'll need to basically rewrite them one by
one
-aaron
I suspect that when the OP asked for an "easy" way, he was hoping to do it
by simply pressing a button!
You have mentioned Now() and Date(), but of course there are hundreds of
Access/Jet functions that could have been used that will need to be
converted somehow, not to mention user-defined functions written in VBA.
Also, anywhere that a query grabs a parameter directly from a form will need
to be looked at and may require some redesign of the application, not just
the query.
A
aaron.kempf
yeah.. it's not super-easy; but if you're doing this for hundreds of
queries-- then you need to come up with some automated solution
yeah.. wherever you have parameters; you need to change it to use a
lookup table..
i usually make a table where i store all my variables
select EmployeeID from SysAppMySettings WHERE SPID = @SPID
it's nice to do things this way since SPID is a constant using ADP
-aaron
queries-- then you need to come up with some automated solution
yeah.. wherever you have parameters; you need to change it to use a
lookup table..
i usually make a table where i store all my variables
select EmployeeID from SysAppMySettings WHERE SPID = @SPID
it's nice to do things this way since SPID is a constant using ADP
-aaron