SQL Backend
-
Hi Guys
I have a database which has very little data stored in it at the moment. In the future, the size of the database will explode when new patient data is imported - it will exceed the 2GB limit imposed in access.
Ideally, I would like to link some tables up to an SQL server, thus allowing the 2GB limit to be exceeded.
Does anyone know of any guides which will explain, in step-by-step detail, of how to link certain tables to a backend SQL server?
Many thanks
Phil
-
Why not use sql server exclusively as the back end and use access only as the front end (research access data projets).
-
In all honesty, i never even knew i could do that. Thanks for the info.
-
HTH.
-
One other solution is to use an Access.mdb file for each table, so long as each table does not exceed 2gb. Then link them into one database for processing. But SQL and .ADP files are the way to go.
-
MS has some simple step by step articles for upsizing from Access to SQL, and there is a wizard in Access that will do it almost automatically. All the Access/Jet dataengine components get removed, and the actual data is moved into a sql server or MSDE if you have that.
-
You can Upsize your access tables to sql server, in access there is a wizard for that, and later you can link to that sql server and keep the same table name que you make the links using odbc, try in the file/obtain external data/link tables menu.
Regards
-
Don't use the upsizing wizzard
.Re-develop your database based on SQL Server functionality and data types. Build it correctly the first time based on published best practices
for database design, performance, security and scaleability.Use the correct tool for the job, so get the sludge-hammer ready your going to be breaking some major rocks
.have fun, realy.
-
My advice is to start with MSDE (free), and then when you reach that critical point, break down and buy sql server.
MSDE is basically a lite version of Sql Server 2000. It has limits, but these limits are there artificially (as in , Microsoft puts them there on purpose)
The key advantage of using MSDE, is that it IS sql server 2000 , so you have an upgrade option.
If you code to access, and then want to go to sql server, it is a HORRIBLE experience, wizards or no wizards.
The biggest pain in the butt with MSDE, is that you only get one free tool to create your tables and such.
It is called osql.exe
Its a command line thing.
Here is some sample code:
To give you a feel for how to use the MSDE:
Create a file in notepad called:
CreateDatabaseScript.sql
/* */
/* CreateDatabaseScript.SQL - Creates the MyFirstDatabase database */
/* */
/*
*/
SET NOCOUNT ON
GO
set nocount on
set dateformat mdy
USE master
declare @dttm varchar(55)
select @dttm=convert(varchar,getdate(),113)
raiserror('Beginning InstMyFirstDatabase.SQL at %s ....',1,1,@dttm) with nowait
GO
if exists (select * from sysdatabases where name='MyFirstDatabase')
begin
raiserror('Dropping existing MyFirstDatabase database ....',0,1)
DROP database MyFirstDatabase
end
GO
CHECKPOINT
go
raiserror('Creating MyFirstDatabase database....',0,1)
go
/*
Use default size with autogrow
*/
CREATE DATABASE MyFirstDatabase
GO
CHECKPOINT
GO
USE MyFirstDatabase
GO
if db_name() <> 'MyFirstDatabase'
raiserror('Error in InstMyFirstDatabase.SQL, ''USE MyFirstDatabase'' failed! Killing the SPID now.'
,22,127) with log
GO
execute sp_dboption 'MyFirstDatabase' ,'trunc. log on chkpt.' ,'true'
GO
raiserror('Now at the create table section ....',0,1)
GO
// END CreateDatabaseScript.sql file
Then, you'll have to run the free tool microsoft gives you:
(from DOS command prompt)
osql -E -i CreateDatabaseScript.sql -o CreateDatabaseScript_OUT.txt
All of the code is in the .sql file, you just have to use the free tool to run it.
Here are some more samples:
-- New file in notepad.exe called CreateTablesScript1.sql
Use MyFirstDatabase
GO
if exists (select * from sysobjects
where id = object_id('Location'))
DROP TABLE Location
CREATE TABLE dbo.Location (
--leave this one in ...
LocationUUID uniqueidentifier NOT NULL DEFAULT NEWID() ,
LocationID int NOT NULL UNIQUE ,
LocationName varchar(128) NOT NULL UNIQUE ,
--leave these, these keep track of how fresh the data is or isn't
CreateDate datetime NOT NULL DEFAULT getDate(),
UpdateDate datetime NOT NULL DEFAULT getDate() ,
RemoveDate datetime NULL
,
CONSTRAINT loc_key UNIQUE ( LocationUUID )
)
GO
--end the file called CreateTablesScript1.sql
Then (again) run the file:
osql -E -i CreateTablesScript1.sql -o CreateTablesScript1_OUT.txt
Then ( here we go again)
Create a file in notepad called PopulateDataScript1.sql
--start PopulateDataScript1.sql
Use MyFirstDatabase
GO
INSERT INTO dbo.Location (LocationID , LocationName) VALUES ( 101 , 'Raleigh' )
INSERT INTO dbo.Location (LocationID , LocationName) VALUES ( 102 , 'Durham' )
INSERT INTO dbo.Location (LocationID , LocationName) VALUES ( 103 , 'Wake Forest' )
INSERT INTO dbo.Location (LocationID , LocationName) VALUES ( 104 , 'DeadMan' + CHAR(39) + 's Bluff' ) --avoid single quotes if possible, but here is an example if you need it
--end PopulateDataScript1.sql
So , its a little hump to get over.
But when you create your database, tables, and maybe data through scripts (instead of the GUI tools), you can easily recreate your enviroment against ~~any Sql Server 2000 database (including its baby cousin called MSDE)
...
Installing MSDE
Unforunately, Microsoft has you jump thru a few hoops on this one.
Create a file called
MyParameters.ini
Inside it (in notepad) , put this:
[Options]
DISABLENETWORKPROTOCOLS=0
SECURITYMODE=SQL
--- end MyParameters.ini
Now, when you download MSDE from Microsoft, you will unzip it, and there will be a setup.exe program.
But double clicking it won't do you much good.
You will then put your MyParameters.ini file in the same directory.
And then run this: (from dos command line)
setup /settings "MyParameters.ini" SAPWD="sapassword" /L*v C:/MSDELog.log
There are different ways, but this will get you started.
After you install MSDE, then you can run those script samples I gave you.
If you make this decision now, you will be very happy in the future.
If you stay with Access, and also use the GUI to create your tables and such, you will have remorse.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply