General discussion

Locked

Access and Sql Server

By eldarai ·
How to transform an Access application made, to a client / server Access/Sqlserver application without beginnig from the start.(i e possibility of reusing the existing application)

This conversation is currently closed to new comments.

7 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Access and Sql Server

by Shanghai Sam In reply to Access and Sql Server

First, you must have your application set up properly in Access. This means that your Access application should have two Access databases. The "back end" database should only contain the tables. All of the rest (queries, forms, reports, macros, etc.) will be the "front end" (or application). You then link to the tables in the back end.

This can be easily done by making two copies of your current combined database. Call one copy mydbBE.mdb ("back end") and the other mydbFE.mdb ("front end").

Then Delete everything except the tables from mydbBE.mdb.

Delete all of the tables from mydbFE.mdb.

Open mydbFE.mdb, click on Files, Get external data, and Link tables, then select mydbBE.mdb and select all of the tables.

You can now run your application in Access by opening mydbFE.mdb.

The SQL data transformation utility will easily move your tables (both the table definitions and the data) from your Access back end database (mydbBE.mdb) into a SQL database.

Once you have set up the proper client connectivity to the SQL database including ODBC drivers, you can use your existing Access application (mydbFE.mdb) by linking all of the tables to the SQL database. Just start the link tables utility (Tools/Add ins/Linked Table Manager) and defining the path for the tables to the SQL database.

While you may not see the full benefit of the SQL back end because the Access queries may be executed at the workstation, rather than at the database server, you should be able to run your application with little or no changes (depending upon the version of Access and which version of VBA you used).

This method should then give you the breathing room to re-write the application in VB or some other language that can take better advantage of the ability of SQL to perform many processes at the server, thereby reducing network traffic.

Collapse -

Access and Sql Server

by eldarai In reply to Access and Sql Server

Poster rated this answer

Collapse -

Access and Sql Server

by Bob Sellman In reply to Access and Sql Server

I'm Bob Sellman, not User Deleted.

First, you must have your application set up properly in Access. This means that your Access application should have two Access databases. The "back end" database should only contain the tables. All of the rest (queries, forms, reports, macros, etc.) will be the "front end" (or application). You then link to the tables in the back end.

This can be easily done by making two copies of your current combined database. Call one copy mydbBE.mdb ("back end") and the other mydbFE.mdb ("front end").

Then Delete everything except the tables from mydbBE.mdb.

Delete all of the tables from mydbFE.mdb.

Open mydbFE.mdb, click on Files, Get external data, and Link tables, then select mydbBE.mdb and select all of the tables.

You can now run your application in Access by opening mydbFE.mdb.

The SQL data transformation utility will easily move your tables (both the table definitions and the data) from your Access back end database (mydbBE.mdb) into a SQL database.

Once you have set up the proper client connectivity to the SQL database including ODBC drivers, you can use your existing Access application (mydbFE.mdb) by linking all of the tables to the SQL database. Just start the linktables utility (Tools/Add ins/Linked Table Manager) and defining the path for the tables to the SQL database.

While you may not see the full benefit of the SQL back end because the Access queries may be executed at the workstation, rather than at the database server, you should be able to run your application with little or no changes (depending upon the version of Access and which version of VBA you used).

This method should then give you the breathing room to re-write the application in VB or some other language that can take better advantage of the ability of SQL to perform many processes at the server, thereby reducing network traffic.

Collapse -

Access and Sql Server

by eldarai In reply to Access and Sql Server

Thanks to you Bob, for your good answer . I 'm suggesting to you another good answer that Mike Threlkeld sent to me. Bye
Georges Eldarai
1)In Access2000, you can use the upsizing wizard to upsize your app to be a client/server app. It gives youthe option of converting your existing app to be a Microsoft Access Project. This will setup your app to use ADO for data access, move your tables and data to SQL Server, and convert your queries to Views and Stored Procedures. Of course, your app is not perfect when the wizard is finished, review the table structures once they're upsized, etc., but it will get you a good start.
2) Or, you can use the upsizing wizard to just move your tables and data to SQL server but leave the app as-is. Ifyou do this, be sure to start immediately using Pass-Through queries as much as possible. This will move the query processing to the SQL Server and away from MSAccess and linked tables. Beware that if you go this route, you might experience some degradation of performance until you're using Pass-Through queries as much as possible because a table linked to SQL Server will be slower than a native MSAccess table. But, the potential beneift is great because Pass-Through queries use the SQL Server to process your queries and not MSAccess.

Collapse -

Access and Sql Server

by miket In reply to Access and Sql Server

1) In Access2000, you can use the upsizing wizard to upsize your app to be a client/server app. It gives you the option of converting your existing app to be a Microsoft Access Project. This will setup your app to use ADO for data access, move your tables and data to SQL Server, and convert your queries to Views and Stored Procedures. Of course, your app is not perfect when the wizard is finished, review the table structures once they're upsized, etc., but it will get you a good start.

2) Or, you can use the upsizing wizard to just move your tables and data to SQL server but leave the app as-is. If you do this, be sure to start immediately using Pass-Through queries as much as possible. This will move the query processing to the SQL Server and away from MSAccess and linked tables. Beware that if you go this route, you might experience some degradation of performance until you're using Pass-Through queries as much as possible because a table linked to SQL Server will be slower than a native MSAccess table. But, the potential beneift is great because Pass-Through queries use the SQL Server to process your queries and not MSAccess.

Mike Threlkeld
CedarView Custom Software, Inc.
Fayetteville, AR

Collapse -

Access and Sql Server

by eldarai In reply to Access and Sql Server

Thank you Mike, your answer is very helpfull for me. I received another answer but yours seems to be the more judicious.
Bye
Georges Eldarai

Collapse -

Access and Sql Server

by eldarai In reply to Access and Sql Server

This question was closed by the author

Back to Web Development Forum
7 total posts (Page 1 of 1)  

Related Discussions

Related Forums