469,927 Members | 1,853 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,927 developers. It's quick & easy.

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 1286
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
By using this site, you agree to our Privacy Policy and Terms of Use.