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.

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.


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

Get Actionable Data from Your Monitoring Solution

Your communication platform is only as good as the relevance of the information you send. Ensure your alerts get to the right people every time with actionable responses. Create escalation rules that ensure everyone follows the process and nothing is left to chance.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
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 …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

717 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