Converting existing MS Access Database to use MSSQL Tables

[rhwiley]

I have a MS Access 2003 database that was originally created for one
user to add data and run reports/queries.

The problem is that there's a heavy push to modify this database so
several people can read (and in some cases add) data to one central
location. They also want the same reports ran.
Anyway, I was able to copy the tables onto a MS SQL server. However,
I'm trying to figure if I can use an ADO connection to take the place
of the Access tables (so the user wouldn't need to configure an ODBC
connection). I did something similar a few years ago using MS Exel and
VB Macro's, but have no idea where to start for MS Access. I'd
appreciate any ideas anyone might have. Thank you.

Bob Wiley

 

[Ed Adamthwaite]

Hi Bob,
You don't need to go to the expense and complication of SQL Server if you
envisage only a few users for the database. If you split the database:
1/ All tables in a single mdb on a server or pc on the network.
2/ All forms, queries, reports, code etc compiled into an mde frontend on
each of the users' workstations with the tables as links to the backend mdb
file.

Access is fairly happy with up to a max of 15~20 users. The backend can be
as big as 2G, but this size does slow things down. You just need to manage
the backend with regular compacting and archiving of old records to another
mdb to keep everything sweet.

Ed Adamthwaite

 

[rhwiley]

Ed,

Thanks for the idea. I thought the SQL server might be a good solution
since I already have one up and it I need a central repository for
information (since I don't want 10-30 versions of the same database
running around).

I'll play around with your idea to see how to make it run. I'm sorry
for being OT on this, but (in the queries) how do I reference the MDB
file with the tables in the central directory? Currently, they're
pointing to the tables locally. I'm sorry for sounding dense, but I've
just never tried doing this type of thing this way before (usually in
the past I'd write a ASP or PHP web page, but the desire is for the
existing reports to be used).

Thanks!

Bob

 

[Dave Patrick]

Why not just create linked tables to the SQL backend? You initially need a
DSN to create the links but then the connection string is stored in Access
hidden table MSysObjects 'connect' column.

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
|I have a MS Access 2003 database that was originally created for one
| user to add data and run reports/queries.
|
| The problem is that there's a heavy push to modify this database so
| several people can read (and in some cases add) data to one central
| location. They also want the same reports ran.
| Anyway, I was able to copy the tables onto a MS SQL server. However,
| I'm trying to figure if I can use an ADO connection to take the place
| of the Access tables (so the user wouldn't need to configure an ODBC
| connection). I did something similar a few years ago using MS Exel and
| VB Macro's, but have no idea where to start for MS Access. I'd
| appreciate any ideas anyone might have. Thank you.
|
| Bob Wiley
|

 

[Douglas J. Steele]

Once you've created the linked tables in the first place, you can then
convert them to DSN-less, so that it's not necessary to create the DSN on
each machine that uses the database.

See http://www.accessmvp.com/DJSteele/DSNLessLinks.html for one way to do
this.

 

[Dave Patrick]

Yes, I know hence the suggestion.

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
| Once you've created the linked tables in the first place, you can then
| convert them to DSN-less, so that it's not necessary to create the DSN on
| each machine that uses the database.
|
| See http://www.accessmvp.com/DJSteele/DSNLessLinks.html for one way to do
| this.
|
| --
| Doug Steele, Microsoft Access MVP
|
| (no e-mails, please!)

 

[david epsom dot com dot au]

Go to File, Get External Data, Link Tables

In "Files Of Type", select ODBC databases. You will
need to have a DSN set up for your ODBC database.

The tables may come through as 'dbo.tablename', in which
case you will want to rename the dummy tables back to the
original name so that you existing queries and reports
still work without change.

Once you have done the connection, there is nothing left
to configure.

Later, you can recode this so that you don't have to have
a DSN on the user machine.

(david)