Creating New MDB, easily upsizeable to SQL Server (1 Viewer)

[Pleasure]

Dear friends.

I want to create a new MDB Back End DB with many tables, fileds of all kinds (even autonumbers and yes/no fields) and of course relationships

Perhaps in the future I'm going to upsize this database to SQL server.

So the question is : What should I take care with this mdb Back End, regarding tables, fields, relationships etc, just to make an easily ... one click away ... from upsizing to SQL Server ?

Thanks in advance.

 

[MagicMan]

The easy answer is to create the tables, forms, macros, VB code using SQL Server Express 2005 (free edition). Then, pull back the tables as local tables and remove the SQL Server links, and rename the local tables, and create a JET BE for use until you need to go SQL Server. Qustion from me is...Why not just develop using SQL Server?

 

[Pleasure]

Is it posible to do this just with Access, without using of SQL Express ?

 

[Banana]

Well, if you go to support.microsoft.com and search for a Jet/ODBC connectivity whitepaper, it has a section of how datatypes are mapped to SQL server. The whitepaper, however, is a bit dated, and I don't know how much different SQL server is from whatever version the whitepaper considered.

Generally, as long you stick to SQL-standard datatype, you should be OK. Examples of non-SQL-standard datatypes (off the top of my head) would be Currency and maybe Yes/No datatype; you also should be able to google for SQL standard and read up on ODBC documentations in addition to the whitepaper to find out how it maps the datatypes.

Still, I have to ask the same question as MagicMan; why not just develop it with the express edition and upgrade to full version, which I'm assuming is less painful than upsizing from Access .MDB to full SQL server...

 

[MagicMan]

Differences between Sql Server and JET
http://sqlserver2000.databases.aspf...ifferences-between-access-and-sql-server.html

Yes you can just do Access Jet, and not have many problems. The relationships go bye bye in Access when you create an SQL Server BE. You can recreate the Relationships, but you really do not need to, since they are now maintained in SQL Server. Your prime differences are Security, and the processing difference on SQL Server. Once on SQL Server, you will find the desire to do processing via Stored procedures and pass through SQL which allow the server to do the crunch work and pass back only the result set.

 

[gemma-the-husky]

some things change

i think a memo field for instance, is much smaller in SQL (2000 bytes isnt it?)

some table level validations dont work for SQL either.

 

[Pleasure]

Thanks a lot for your replies.

I will create the DB with SQL Server Express 2005 (is express 2008 free)?

Then I will just link to the SQL Server Back End and everything will be ok (I guess). I hope I can use JET SQL in queries of the Front End rather than T-SQL. The point now is this : Regarding performance, is it better to have the queries on Ms Access F/E rather than on SQL Server B/E ?

Thanks everyone again ...

 

[Banana]

Again, if you read the whitepaper I told you, it would give you the information about how Jet processes queries and how this affects the performance.

Basically, call queries from SQL Server BE if you have quite complex criteria or something that would require evaluation and could possibly end up getting too many data for Jet to evaluate locally. Otherwise, it'll be fine in the FE.

 

[Pleasure]

Thanks for the reply Banana. Actually I thought that Access queries, even when linked to SQL Server, are running localy and not on the BE SQL Server. So on an Intranet or LAN environment, perhaps this would make the Access db performing very slow.

Unfortunately I couldn't locate the whitepaper that you mentioned. Perhaps if you would share with me a link would be very helfull.

Also do you know if SQL Server Express 2008 is free ?

Thanks again for your help as also everyone for helping dummies like me

 

[Banana]

Here.

Pretty sure express edition, whether 2005 or 2008 is free.

 

[Pleasure]

Thanks Banana but not working with Vista 64.

 

[Pleasure]

Ok it worked with just extracting (using winzip) the word document that was inside it.

 

[Banana]

Sorry about that. I honestly do not get why they feel the need to put it in a .exe, not a simple .zip file. They're insane, if you ask me.

 

[gemma-the-husky]

upsizing an Access DBS to SQL is not the same as just putting the backend on a different environment

I am not expert in SQL, but I do know that if you just try to treat the backend as an alternative to Access Jet, it may actually be slower!

You have to redesign the database to take advantage of stored procedures etc, to ensure that data is processed on the backend as far as possible

Any experts out there with more info?

 

[Banana]

Gemma,

Again, you would have to read the whitepaper I referenced which does details how Jet manage ODBC data and what you can do to make it more effective.

You are correct about taking advantage of stored procedures, but understand that this isn't always strictly necessary. Local queries actually can be just dandy for the job. It's best to optimize whatever needs, rather than trying to pre-emptively optimizing (which can get you worse results!).

 

[MagicMan]

Pleasure, the decision to go SQL Server Express takes a few simple answers:
1) Free SQL Server 2005 vs Free SQL Server Express 2008 ... 2005 is well founded, 2008 is new and if the data is critical, I like to go with the solid well used...2005.
2) The SQL Server Express 2005 bundle comes with Management Studio. With 2008, you have to use the 2005 Management Studio. (2005 has more 3rd party tools as well...not to say they won't work with 2008)

SQL vs JET
1) Sql Server has far better security for your data!
2) Sql Server is more complicated to use if you need to develop code behind the forms. Basically you have to learn more things. Access/Sql Server/ADO/T-SQl/VB/DAO. With Access you need to know Access/VB/DAO at most, and for simple non complicated applications, just Access.
3) Data volume. Access is limited, SQL Server in most respects is not. Access is limited to 2GIG, which is a heck of a lot of data, so this may not apply.
4) Performance....Jet is faster with smaller databases...SQL Server is faster with larger tables when doing searches. Both are equally fast on single item lookups and updates.
5) Field level security....with SQL Server views, it is easier to permit restricted access to user groups, right down to the field level.
6) Access does not pull all the data back from SQL Server to do functions. This only occurs when the code (written in vba) cannot be translated by Access into a valid SQL Server request. Even then, the SQL Select/Insert/Update can be delivered to SQL Server via a Pass Through Query, which bypasses the Access translation process and delivers the SQL request directly to SQL Server. In addition, SQL Server can have stored procedures which can be executed and run on the server with only the result set returned.

Long and short...Small to medium databases with average to complex queries and intermediate or less programming skill...use Access. Good knowledge of VBA/DAO/ADO/SQL Server/T-SQL or the ability to use the forums well...with large databases and some very complex queries...SQL Server.

Smiles
Bob

 

[Pat Hartman]

I start the majority of my applications with local Jet tables regardless of whether or not the back end will be Jet or some other RDBMS. The reason is simple - in a corporate environment (most of my clients are large), they will simply not grant me free access to the SQL server (DB2, Oracle, Sybase, etc.) test region. I'm not sure why they call them test regions but the client organization seems to want their own DBA (database administrator) to do all structure work. Needless to say, if you change the tables on a daily basis, the DBA will stop taking your phone calls so I start with Jet and when the structure is pretty stable, I let the DBA upsize it and continue development against the server back end. I have grown so used to this model that even in environments where I do have the necessary permissions to do my own DBA work, I still work with Jet until the schema is stable.

Even when I am pretty sure that the app will never be upsized, I still create the app with client/server techniques in mind since they do not adversly impact the performance when the app is linked to Jet and they dramatically improve its portability should the situation change.