Implement SQL server on previous Access Apps

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?
Who is Participating?
mativareConnect With a Mentor Commented:
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)

Victor SpiridonovCommented:
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.
stAuthor Commented:
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.
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Victor SpiridonovCommented:
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.

stAuthor Commented:
These articles seems related to MS Access only, do you have any suggested articles about working MS Access with MS-SQL server?
Victor SpiridonovCommented:
>>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.

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
stAuthor Commented:
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.
stAuthor Commented:
Learn and following your answer........Thanks......
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.