Convert Access DB to SQL

[Mark]

Hi -

The physical size of my Access database is getting quite large (it expands
to 1.2Gigs but comacts to 0.5Gig) and I think I will need to convert it to
SQL in the not-too-distant future, although Access still runs.

I'm not sure how to get started, and I am asking for suggestions on how to
convert a Access database. I know Access fairly well (hence the extensive
use of Access), but I'm not familiar with SQL databases (although I know the
SQL language).

About the Current Database:

The Access database copies tables from a SQL database, then runs a series of
queries and VBA modules (DAO) that produce final tables which are exported in
Excel format. (These queries/modules create interim tables along the way.)

The SQL source database resides on our server. The Access database is on my
desktop or laptop, so the current processing is on the desktop.

There are no users other than myself and I run the main VBA module that
exports the Excel files.

If life was easy I would do the following (keep in mind that I'm a newbie,
and don't really know much about the conversion process):

Copy the current Access database (tables, queries and VBA modules) into a
new SQL database (different than the source SQL database).

Do the processing on the server, instead of on my desktop/laptop.

Use the Access interface to make changes to tables/queries/VBA modules.

Thank you for your suggestions,
Mark

 

[Dave Patrick]

I'd start by linking to the tables on SQL server. Create an ODBC DSN to SQL
server (Control Panel|Admin Tools|Data Sources), then in the Access tables
window right-click|Link Tables then 'Files Of Type' choose ODBC Databases,
then select the DSN you just created, then select all of the desired tables
and OK (note that the linked tables now all have names such as
dbo_tablename. Now you can delete the local tables and rename each linked
table stripping off the dbo_

(Also note that any constraints and or relationships are now handled by SQL
server.)

This should speed things up a bit but I'd guess that the temp tables you're
creating are the main source of the bloat.

--

Regards,

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

:
| Hi -
|
| The physical size of my Access database is getting quite large (it expands
| to 1.2Gigs but comacts to 0.5Gig) and I think I will need to convert it to
| SQL in the not-too-distant future, although Access still runs.
|
| I'm not sure how to get started, and I am asking for suggestions on how to
| convert a Access database. I know Access fairly well (hence the extensive
| use of Access), but I'm not familiar with SQL databases (although I know
the
| SQL language).
|
| About the Current Database:
|
| The Access database copies tables from a SQL database, then runs a series
of
| queries and VBA modules (DAO) that produce final tables which are exported
in
| Excel format. (These queries/modules create interim tables along the
way.)
|
| The SQL source database resides on our server. The Access database is on
my
| desktop or laptop, so the current processing is on the desktop.
|
| There are no users other than myself and I run the main VBA module that
| exports the Excel files.
|
| If life was easy I would do the following (keep in mind that I'm a newbie,
| and don't really know much about the conversion process):
|
| Copy the current Access database (tables, queries and VBA modules) into a
| new SQL database (different than the source SQL database).
|
| Do the processing on the server, instead of on my desktop/laptop.
|
| Use the Access interface to make changes to tables/queries/VBA modules.
|
| Thank you for your suggestions,
| Mark