472,119 Members | 1,584 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,119 software developers and data experts.

Migrate VIEW from Access to SQLserver ???


I migrated a DB from Access 2000 to SQLserver 2000.

The tables are OK, but I see the old Access views... as TABLE in
SQLserver!!

Any suggestions?

--

Jul 20 '05 #1
3 1336
Access isn't "smart" enough to upsize its queries into MS SQL Server views
so instead it just runs the query and creates a table from the result.
You'll have to port all of the queries manually (although I'm sure there are
tools or at least guides to help you with this) since Access's SQL differs
from MS SQL Server's SQL. Some of the most common issues I've run in are
that MS SQL Server doesn't have all those handy VB functions and IsNull()
does not work the same way in both.

"YURYSSG" <yu*****@yahoo.it> wrote in message
news:2i************@uni-berlin.de...

I migrated a DB from Access 2000 to SQLserver 2000.

The tables are OK, but I see the old Access views... as TABLE in
SQLserver!!

Any suggestions?

--

Jul 20 '05 #2

"Jonathan Amend" <ce*******@hotmail.com> /
: news:40bf8fc4$1_3@aeinews....
Access isn't "smart" enough to upsize its queries into MS SQL Server views
so instead it just runs the query and creates a table from the result.
You'll have to port all of the queries manually (although I'm sure there are tools or at least guides to help you with this) since Access's SQL differs
from MS SQL Server's SQL. Some of the most common issues I've run in are
that MS SQL Server doesn't have all those handy VB functions and IsNull()
does not work the same way in both.

"YURYSSG" <yu*****@yahoo.it> wrote in message
news:2i************@uni-berlin.de...

I migrated a DB from Access 2000 to SQLserver 2000.

The tables are OK, but I see the old Access views... as TABLE in
SQLserver!!

Any suggestions?

--


Jul 20 '05 #3
"Jonathan Amend" <ce*******@hotmail.com> wrote:
Access isn't "smart" enough to upsize its queries into MS SQL Server views
so instead it just runs the query and creates a table from the result.
You'll have to port all of the queries manually (although I'm sure there are
tools or at least guides to help you with this) since Access's SQL differs
from MS SQL Server's SQL. Some of the most common issues I've run in are
that MS SQL Server doesn't have all those handy VB functions and IsNull()
does not work the same way in both.


FWIW I was able to programmatically make views out of many of my
Access queries.

I had much more code than this but the basics were as follows.

strNewSQL = adhReplace(Q.SQL, vbCrLf, " ")
strNewSQL = Left(strNewSQL, InStr(strNewSQL, ";") - 1)
strNewSQL = ConvertTrueFalseTo10(strNewSQL)

tagRetryAfterCleanup:
Set myquerydef = dbsPermanent.CreateQueryDef("")
'Q.Name & " DAO Test")
myquerydef.ReturnsRecords = False
myquerydef.Connect = strConnect
myquerydef.SQL = "CREATE VIEW [" & strQueryName & "]
AS " & strNewSQL
myquerydef.Execute
myquerydef.Close

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Jul 20 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by SJM | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.