upgrading to MSDE

[gianniiiii]

Hy all,

i have created a database with access 2003 that now experience some slowness problem. i have concluded that is a table that is becomed to big, but i can't delete any record...

it should help me upgrade the database to MSDE? if yes someone could help me?

MAx

 

[Uncle Gizmo]

>>> upgrade the database to MSDE? if yes someone could help me? <<<

Do you mean access database project, or do you mean MS SQL?

Or both?

Please could you supply details like number of records, number of users, is it used over and Internet, do you have a front end back end arrangement ,

just some basic useful information please.

 

[RuralGuy]

Uncle_Gizmo,
MSDE is a 5 user limited version of MS SQL, in case you were not aware that comes free with Access.

 

[boblarson]

MSDE has the SAME file size limitation that Access has, so that won't help you. Have you compacted the database?

 

[gianniiiii]

>>> upgrade the database to MSDE? if yes someone could help me? <<<

Do you mean access database project, or do you mean MS SQL?

Or both?

Please could you supply details like number of records, number of users, is it used over and Internet, do you have a front end back end arrangement ,

just some basic useful information please.

ok,

My database is build in this way, 2 mdb files, one contains the interface (forms, querys etc), the other contains only the tables.
i have 10 users: 5 form different pc's on the network, the other 5 connected to the server in terminal server.

i wanna move the tables in MSDE and (possibly) continue to use access as the interface.

Max

 

[gianniiiii]

Uncle_Gizmo,
MSDE is a 5 user limited version of MS SQL, in case you were not aware that comes free with Access.

hy rural guy, is still limited to 5?

Max

 

[gianniiiii]

MSDE has the SAME file size limitation that Access has, so that won't help you. Have you compacted the database?

hy the problem is not the limit (you mean the space?) of the database...is that now this table as a lot of record and is becomed slow

Max

 

[RuralGuy]

As far as I know. More users requires the full product which requires $$$$.

 

[gianniiiii]

As far as I know. More users requires the full product which requires $$$$.

do you have allready upsized a database to sql?

 

[RuralGuy]

MSDE has the SAME file size limitation that Access has, so that won't help you. Have you compacted the database?

Are you sure about that Bob? It really is just a user limited full version of MSSQL which has no such limitation as far as I know. Unless using Access as the front end limits the size. I think I'll do some more research and post back later.

 

[boblarson]

Moving from Access as a backend to SQL Server or MSDE requires some planning. You need to decide whether you are going to need local temporary tables. If so, you can't use an Access Data Project. However, if you use an MDB for the frontend, then you need to incorporate some SQL Server error handling as transactions may appear to have completed when in fact they did not. You will need to code for it to return the error message to you so you can know whether the transaction actually committed.

If you don't need local temporary tables, then you can use an Access Data Project as it will take care of the transactional details and error raising for you.

However, whichever way you go, you will need to make use Stored Procedures for parameterized queries and action queries and the conversion process to "upsize" a database has several key "gotchas" which you have to be aware of BEFORE you upsize.

Just so you are aware that it's not just a quick and simple process to move from one to another.

 

[boblarson]

Rural Guy:

From the MSDE Webpage:

Maximum Database Size
MSDE 2000 supports up to 2 gigabytes (GB) per database. This limitation is per database, not per server. A single computer can support multiple MSDE 2000 instances, each with databases of up to 2 GB in capacity.

That is the same for Access.

 

[RuralGuy]

hy the problem is not the limit (you mean the space?) of the database...is that now this table as a lot of record and is becomed slow

Max

Maybe something here will help. Especially limiting record selection to *only* what is required for the task at hand.

 

[RuralGuy]

Rural Guy:

From the MSDE Webpage:

Maximum Database Size
MSDE 2000 supports up to 2 gigabytes (GB) per database. This limitation is per database, not per server. A single computer can support multiple MSDE 2000 instances, each with databases of up to 2 GB in capacity.

That is the same for Access.

Thanks BL. Now I'm just a little wiser than I was a couple of minutes ago. Appreciate the heads up.

 

[gianniiiii]

Moving from Access as a backend to SQL Server or MSDE requires some planning. You need to decide whether you are going to need local temporary tables. If so, you can't use an Access Data Project. However, if you use an MDB for the frontend, then you need to incorporate some SQL Server error handling as transactions may appear to have completed when in fact they did not. You will need to code for it to return the error message to you so you can know whether the transaction actually committed.

If you don't need local temporary tables, then you can use an Access Data Project as it will take care of the transactional details and error raising for you.

However, whichever way you go, you will need to make use Stored Procedures for parameterized queries and action queries and the conversion process to "upsize" a database has several key "gotchas" which you have to be aware of BEFORE you upsize.

Just so you are aware that it's not just a quick and simple process to move from one to another.

mmm.. ok i'have never used a data project before.. you now where i can found some documentation?

 

[gianniiiii]

Just so you are aware that it's not just a quick and simple process to move from one to another.

as you can understand i'm not an expert but the problem is also if i use access linked table to the MSDE?

(hoping to have'nt make a stupid question )

Max

 

[boblarson]

Here are a couple of places to look:

http://support.microsoft.com/default.aspx?scid=kb;en-us;237980

Make sure to read this about error handling:

http://www.sqlteam.com/item.asp?ItemID=2841

 

[Pat Hartman]

I recommend not creating an .adp. Microsoft will still support them but little effort will be put into improving them.

When moving a database from a Jet be to an ODBC be, several things need to be considered.
1. All forms MUST be based on queries and those queries MUST limit the number of rows being returned. Given the large size of your tables, you may already be doing this.
2. If your column names are non-complient with SQL server standards, you will be faced with making modifications to your application to accomidate that. Eliminate any spaces or special characters except the underscore from you names. Also avoid any name that is the same as a VBA or SQL property or function. So names like "Name" or "Date" or "Year" will cause problems or be rejected entirely.
3. If you are using user defined functions in your queries, they must be removed.
4. If you are using VBA functions in your queries they must be removed.
5. Search the MS knowledge base for articles on client/server optimization.