Implement SQL server on previous Access Apps

Posted on 1998-12-21
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.

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)


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 120 total points
ID: 1092355
If you have not ODBC driver for MSSQL Server installed you need to download it from 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Query Task 11 43
SQL Server Sum Over Multiple Tables 20 33
Searching a barcode number within a string. 7 27
T-SQL: problem comparing datetime 4 48
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

749 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