473,597 Members | 2,711 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Possible to keep MS Access interface and migrate the MS Access to MS SQL Server?

Bon
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?

If I migrate the MS Access to SQL Server, would the queries, back-end
VBA, macro, tables and forms be affected? Do I need to change the MS
Access data type to SQL server supported data type?

Which tool I can use to do the migration? Upsizing wizard or exporting
the Access database and then importing it to the SQL server?

Thanks in advance

Cheers
Bon

Oct 31 '05 #1
4 2298
This is somewhat easy.

Basically you need to port or transfer your Access tables to a SQL DB
and then link the Access DB/App. to the SQL tables on the SQL Server.
If you keep the table names the same, the code etc shouldn't need
altering, bu I would test it first.

Oct 31 '05 #2
db55 wrote:
This is somewhat easy.

Basically you need to port or transfer your Access tables to a SQL DB
and then link the Access DB/App. to the SQL tables on the SQL Server.
If you keep the table names the same, the code etc shouldn't need
altering, bu I would test it first.


If only it were that easy :-)
Nov 1 '05 #3
On Tue, 01 Nov 2005 08:09:08 +0000, Trevor Best <no****@localho st.invalid>
wrote:
db55 wrote:
This is somewhat easy.

Basically you need to port or transfer your Access tables to a SQL DB
and then link the Access DB/App. to the SQL tables on the SQL Server.
If you keep the table names the same, the code etc shouldn't need
altering, bu I would test it first.


If only it were that easy :-)


What Trevor is getting at is that it's easy to take an average Access app,
change the tables into links to tables on the server, and have it technically
function. Unfortunately, many of the design decisions commonly built with a
JET back end are not appropriate for a C/S app, and perform dismally in that
context.
Nov 1 '05 #4
Bon wrote:
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?

If I migrate the MS Access to SQL Server, would the queries, back-end
VBA, macro, tables and forms be affected? Do I need to change the MS
Access data type to SQL server supported data type?
Queries are one thing you'll need to look at. In the first instance
Access may do a direct translation to T-SQL (if you're lucky) otherwise
it can prepare a load of SPs and execute them, sometimes this works and
sometime it results in it effectively bringing across the entrire tables
from SQL Server and performing joins locally, which is bad. YMMV.

If you're moving queries to views, take care if the query uses any built
in or VBA functions as these won't exist in T-SQL. You can either write
a UDF or write the query differently.

In Access the use of "where exists (select...)" performs very badly
compared to "where column in (Select column...)", in SQL Server the
exists method is more efficient.

DAO code may behave unexpectedly, e.g. I had this problem
(http://www.besty.org.uk/memory.htm) but that's since been fixed and I
can't reproduce it now. Using ISAM methods in DAO (.Index, .Seek) will
not work.

Data-types shouldn't be a problem, SQL Server has more than Access,
Access will assume its own types when it sees them on the server. A few
caveats apply:

Access Yes/No is equivalent to SQL Bit but make sure you make it
required and default to 0 else unpredictable results can occur. Also
realise the values are different, in SQL Server its 0 and 1, in Access
its 0 and -1 and most of the time translates OK but if using an Access
query its better to use <>0 as criteria than =1 or =-1 or =True just to
be on the safe side.

Datetime data.
SQL Server only allows back to 1753 or some such, Access allows
(incorrectly as it doesn't handle missing days) further back than that.
You may think this might not concern you but the number of people who
put in 1/12/202 instead of 2002 will cause you immediate problems. If
you have that situation you have a problem already but just not realise it.
Also datetimes are stored differently in each and floating point errors
can occur resulting in the dreaded "#deleted" appearing in rows where
this occurs or "data has changed" errors. A timestamp column in the
table cures this.
Which tool I can use to do the migration? Upsizing wizard or exporting
the Access database and then importing it to the SQL server?


I've not come across a perfect one but in the past I've used the
upsizing wizard as it upsized more in the way of indexes, relationships,
etc.
Nov 1 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

9
7258
by: Daven Thrice | last post by:
If I have a fairly big Access MDB, that is relational, and has, say, 100 objects (forms, reports, modules, etc.), what is the path to get this database "online". Is there a way to put the tables online and then distribute the front end to each user? What if I want to have all the forms and stuff online? How do you approach something like that? Is it a total rewrite with another tool?
3
1560
by: Juergen Lorenz Simon | last post by:
Hello, I'm working on a proposal for migrating a set of MS Access applications over to an Application Server setup. We would want to be capable of running things in parallel for a bit. The idea is to migrate the database schemata and actual data over to a different, more scalable DBMS and keep using the existing MS Access Applications, while we migrate the applications module by module. The question is: is it possible to use MS Access...
2
1617
by: lcifers | last post by:
First off, sorry if my cross posting offends anyone. I'm posting this in Access and SQL Server groups - not sure which one is appropriate. I have a relatively simple ASP.NET/VB.NET application that is now hitting an Access 2000 database over an intranet. We have to migrate the database to SQL Server 7. My experience with ASP.NET is pretty limited and my experience with SQL Server is nonexistent. We have an MSDN subscription, so I went...
9
3297
by: Tony Lee | last post by:
Some time a ago, on this newsgroup the following comments were made in recommending good references for Access (2003) >I used to recommend Dr. Rick Dobson's, "Programming Access <version>" for >people moving from power user to developer, but now I suggest you browse >it, >too. It strongly emphasizes ADO, which knowledgeable Microsoft insiders no >longer recommend, and the Access ADP client to SQL Server. He writes well, >and is a good...
10
1580
by: Steven Spits | last post by:
Hi, Because we have a large WebApp, back in 2002 we decided to use the following method: http://support.microsoft.com/default.aspx?scid=kb;en-us;307467 In short: Create a project "a" at http//localhost/MyWebApp
15
2875
by: Wes Groleau | last post by:
When I try to import from Access, the DTS wizard only allows me to import tables and queries. OK, I'm not surprised the "macros" and reports don't come over. But it executes each query, and created a _table_ to hold the results. The sensible thing would be that SELECT queries become views and the others become stored procedures. But I find no way controls I can select to do that.
10
2432
by: AA Arens | last post by:
I do have a database with customer info in it. To avoid it will be taken out of our office, is it possible to make it not-readable after a certain period? then every let say seven days, I needs to "extend the license", so it will last another week. It consists of queries, forms, and tables, format Access 2003. Bart
6
2208
by: ARC | last post by:
Ok, so I'm looking at Access 2007, and I have imported my existing Access 97 application. I'm feeling a bit overwelmed in what to do here. In my original 97 application, I had one form: Mainmenu, which contained a header area / customer selection area, and a subform. I had a custom toolbar that had a button for each major screen. So clicking Customers would load the customers form into the subform of the mainmenu. By choosing a customer in...
64
4551
by: John | last post by:
Hello there, Im cursing my place of employment...and its taken me a month to realise it... The scenario: Ive just stepped into a role to migrate an access database to VB.Net. The access database runs on terminal services and supports approximatly 25-30 users. It is crapping out big time, corrupted data, changes to the front end are difficult for someone unfamiliar with the system (me), the table structure is bad...really bad....there is...
0
7977
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8276
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
8381
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 captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
8044
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 most users, this new feature is actually very convenient. If you want to control the update process,...
0
6705
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
5847
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5437
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 into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
1
2409
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
1496
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.