Solved

Implement SQL server on previous Access Apps

Posted on 1998-12-21
9
252 Views
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?
0
Comment
Question by:st
  • 4
  • 3
  • 2
9 Comments
 
LVL 7

Expert Comment

by:spiridonov
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.
0
 
LVL 1

Author Comment

by:st
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.
0
 
LVL 7

Expert Comment

by:spiridonov
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.


0
 
LVL 1

Author Comment

by:st
ID: 1092351
These articles seems related to MS Access only, do you have any suggested articles about working MS Access with MS-SQL server?
Thanks.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 7

Expert Comment

by:spiridonov
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.


0
 
LVL 1

Expert Comment

by:mativare
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
0
 
LVL 1

Author Comment

by:st
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.
0
 
LVL 1

Accepted Solution

by:
mativare earned 120 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.
table.

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)



0
 
LVL 1

Author Comment

by:st
ID: 1092356
Learn and following your answer........Thanks......
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

708 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now