Implement SQL server on previous Access Apps

Posted on 1998-12-21
Medium Priority
Last Modified: 2010-08-05
I have some previously Access application want to be implement with SQL server 7 because they are working too slow.

All of them are separate into two parts:Front-end and Back-end. Front-end included Coding & Forms, Back-end is only a .mdb which contains tables only.

I can use DTS to transfer the Back-end into SQL server and I think I should use ODBC but how can I modify my previous coding for referencing the Back-end in SQL server easily?
Question by:st
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2

Expert Comment

ID: 1092348
Theoretically it is as simple as moving all the data from back-end mdb to the server and attaching server tables (using odbc datasource) to the front-end mdb  instead of Access tables. But really you will probably will have to modify some of your forms and code , since you are moving from file server model to client/server model, and it is not a simple issue, that can be explained here. You can search Microsoft Technet first , it has several good articles about optimizing Access front-ends for client/server enviroment.

Author Comment

ID: 1092349
Even it is not easy to do, I really want to know how to do.
Would you please tell me that what kind of coding I need to be change. (It is appreciate that if I can have an example) Thanks.

Expert Comment

ID: 1092350
Proper description would take 10~20 pages.You should better look for the following articles (at least) in Microsoft Knowledge Base:
"Developing Client-Server Applications With Access "
"Creating Large-Scale Applications with MS Access 97"
"Upsizing MS Access Based Applications"
They cover almost all topics you need to be aware off.

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.


Author Comment

ID: 1092351
These articles seems related to MS Access only, do you have any suggested articles about working MS Access with MS-SQL server?

Expert Comment

ID: 1092352
>>These articles seems related to MS Access only
These articles ARE about working in Access with SQL server.

I don't understand what else do you need.  If you need help with some specific issue in integrating Access with SQL server, ask more specific questions.


Expert Comment

ID: 1092353
Besides DTS will not transform indexes and relationships
they are lost. You need to manually recreate them
If your database is really complicated maybe you need to do a trick
and use Access 2000 beta(available for $20 from Microsoft site)
Access 2000 and SQLSErver 7 files are compatible, it means you can actually save into MSSQL server 7. Of course you need to upgrade first to Access 2000, you might save relationships this way.  But communicating Access front end to server is big job  Big companies pay now millions every year for exactly your problem, sorry to dissapoint. One example: think what happens to your  queries, there is no way to easily upsize you need to start create Stored Procedures in server instead and you can use your your queries(script) like skeleton, also Access SQL and TSQL are not fully compatible, therefore lot of manual job.But if your back end is not complicated and small it is still possible.
Good luck.
Besides they carry a book
Access 95 Client/Server Development   (QUE)
By Michelle A. Poolet and Michael Reilly
it explains step by step how to use access front end with servers.Old book but all principles still alive

Author Comment

ID: 1092354
I think all I wanted to know for complete the task should be:
(1) How to install the required ODBC driver to MS Windows 95
(2) In MS Access, how to attach a Table from SQL server.
(3) A simple example for how to do SQL server query in VBA coding of MS Access.

Thanks a lots, additional points can be add for you if you need.

Accepted Solution

mativare earned 480 total points
ID: 1092355
If you have not ODBC driver for MSSQL Server installed you need to download it from
www.microsoft.com. Chanses are you allready have it, it comes with several products
Search you Windows directory for SQLSRVR.dll, this is name of driver.
And you need general ODBC driver manager too, download ODBC SDK
from Microsoft site.
Setup is simple, only follow instructions, it will registrate dll too.
      Next step is to SETUP ODBC CONNECTION
      Controlpanel, click on ODBC icon, select System DSN from data source dialog box
Select Add, the data Source dialog opens showing the list of drivers
Select SQL Sever and click OK
In ODBC Server Setup dialog box add information
Data Source Name(DSN), Description, Server , name then as you wish but memorize DSN
Network address and lIBRARY LEAVE DEFAULT.

      Connecting to server
      Easiest is to use LINKED TABLES method
Create empty database
Then FILE, Get External Data, Link Tables
In Link Dialog Box, for Files Of Dype, select ODBC database from list, this takes you
to Data Source Dialog Box
Now select your DSN, remember you created it in previous section
Now you will get SQL Server Login screen, type username and password
Now select table to link. In your empty database you see a linked table
use it as real.

Code to query server

Set references to DAO
Add Module to project
Create Procedure in MODULE
Insert code into procedure
Dim db as database
Dim Qd as querydef
Dim rs as recordset
Set db = OpenDatabase("C:\yourlinkeddbpath.mdb") ' put 'correct path
Set Qd = db.CreateQuerydef("a")
qd.Connect = "ODBC;dsn=rememberyoucreatedit;uid=insertitoverhere;pwd=insertitoverhereorleaveblank;database=inserverofcourse;"
qd.SQL = "Select cName from Table1 where Id = 2" ' here is 'place where you will
'write your query, modifie it, use TSQL, do not use Access SQL
qd.Returnrecords = TRUE
Ser rs = qd.OpenRecordset
Yourform1.Label1.Caption = rs(0) ' substitute form and control 'names
'Run this code once
'In the future you can run only
Set rs = db.OpenRecodset("a",dbOpenSnapshot)


Author Comment

ID: 1092356
Learn and following your answer........Thanks......

Featured Post

Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question