473,587 Members | 2,316 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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 1378
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*******@hotm ail.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*******@hotm ail.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 programmaticall y 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.SQ L, vbCrLf, " ")
strNewSQL = Left(strNewSQL, InStr(strNewSQL , ";") - 1)
strNewSQL = ConvertTrueFals eTo10(strNewSQL )

tagRetryAfterCl eanup:
Set myquerydef = dbsPermanent.Cr eateQueryDef("" )
'Q.Name & " DAO Test")
myquerydef.Retu rnsRecords = False
myquerydef.Conn ect = strConnect
myquerydef.SQL = "CREATE VIEW [" & strQueryName & "]
AS " & strNewSQL
myquerydef.Exec ute
myquerydef.Clos e

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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
1562
by: aaj | last post by:
Hi all We have some tables with a couple of layers of very simple views built on top. In the table are maybe 6 columns and about 15000 records. The first view cobines the data in the table with some other data from a lookup table. The second view does some sorting on the first view using certain dates . They have worked fine for well over...
2
2320
by: Hennie de Nooijer | last post by:
Because of an error in google or underlying site i can reply on my own issue. Therefore i copied the former entered message in this message. -------------------------------------REPY---------------------------------- Hi Maybe i wasn't clear. I want to dynamically check whether what the lowest date and the highest date is in the calendar table....
4
2298
by: Bon | last post by:
Hello all Would it be possible to migrate the MS Access 2000 to MS SQL Server 2000? My application is using MS Access 2000 as database and as user interface such as forms. Now, I want to migrate the backend database from MS Access 2000 to MS SQL Server 2000. However, I want to keep the MS Access 2000 interface. Would it be possible?
2
2561
by: Vikrant | last post by:
Hi DB2/UDB Gurus, I got the same problem , when I am migrating DB2/UDB 5.2 to 7.2 EE under AIX. Is this a known problem ?, I would sincerely appreciate (well if you want to have money, that is also possible, I am serious)
0
1058
by: SJM | last post by:
I have a situation where I can only gain access to some data that is on a SQL Server database (in another coy division) via a View. I need to manipulate this data back in Access so I would like to extract it into an Access mdb database. I tried to do this using ADO but I am finding it difficult. I was able to test my code out successfully on...
6
2215
by: Shimon Sim | last post by:
Hi I am working on application that need to hold custom user information - Last and first name, email, some other domain related information. I used to create Base class for all my pages. The base class would have CurrentUser property that would hold customer class in session and that was fine for all my situations. Now ASP.NET 2.0 came...
0
1310
by: Gabriele Bertolucci | last post by:
Before saying anything more, I'm sorry for cross-posting, but the topic should be relevant for every newsgroups I posted in. ================================================== Access XP. ADP. A form based on a view from a SQL Express instance ('cause it's a view, it has no parameters). The view contains two INNER JOIN.
5
3205
by: herman404 | last post by:
Hi everyone, I have some code that we need to migrate to SQL Server 2005 from 2000, and I have a webpage that upon viewing, fires a query to the SQL server using ISS and ISAPI. The result set is formatted for display using XSLT. But since ISAPI is deprecated in SQL 2005, I was wondering how to migrate this. Thanks,
0
2098
by: addepad | last post by:
I use an MsAccess frontend app that uses a 2 table view defined in Sql(2005). When i link the view manually I can say (and cache in Access) what field will be used as as primary key. This way I can update data in the view (in both underlying tables). In fact the same that is possible when I open the view via Sql management console. But......
0
8339
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7967
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
8220
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
5392
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3840
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3872
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2347
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1452
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
1185
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

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.