Migrate VIEW from Access to SQLserver ???

"Jonathan Amend" ÓÏÏÂÝÉÌ/ÓÏÏÂÝÉÌÁ × ÎÏ×ÏÓÔÑÈ
ÓÌÅÄÕÀÝÅÅ: 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" wrote in message
...

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?

--

"Jonathan Amend" 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