Please Advise!

[mohammadagul]

Hello friends,
I have been developing an Accounting System for my Multipurpose company with all the facts of accounting. It Includes everthing we need in An Accounting package. Presently we have 4 Users, one administrator. System is going very smooth with FE/BE on normal WindowsXP and MS Access Splitted Database.

Now the present situation is that we have to implement this for 8 user on 4 Story Building. First floor has 3 users, Second had 2 and Third has one for GM and fourth has one For GM.

My suggestion to the Management was to take a SQL Server and upzise the Database. Before i go any further i just wanted to add here that my database is a collection of more than 500 objects, mostly important are the queries. Update, Append, Union and create table queries are in significan amount in the db. Unfortunatley one of my friend had told me that All my Append, update , union and create table queris developed in Access will not work in SQL Server.

My question

1. Is this true that all the queries , Append, Update, Union And Create Table queries will not work?
2. if it is true than what methodology should i adopt to make my queris run?
Should i make functions and write all the coding of my Queries in VB as SQL Statments?

Please advice as best as possible.

Thankyou

Muhammad Atif Gul.

 

[mohammadagul]

two days and not even a single line of answer.
i am sure there must be somthing all the gurus out there can tell me.

looking forward for a reply this time.

Muhammad Atif Gul

 

[namliam]

When using a SQL backend you should not use any createtable statements tho they are possible....

Your append/insert and updates will work as long as you have a PK for every table, a union will work everywhere all the time

Further more I suggest your read this

Regards

 

[Mile-O]

two days and not even a single line of answer.

Probably because you asked at the weekend and very few people use the forum at that time.

 

[The_Doc_Man]

I've never done this myself, but it has been done at my site. You might look in the help files about a wizard that will help you upgrade the DB.

You should be aware that, depending on some things you might use, your DB might encounter a conversion problem or two. HOWEVER, this should not be insurmountable. The conversions are things like realizing that a Yes/No in Access is a BYTE (very short integer) in some DB packages. Currency is stored in a double-long integer (OCTAWORD). Things like that will come up. Usually, you can find conversion information in help files or manuals because any package that CAN talk to Access knows it and will have a conversion manual as a separate document or at least a separate chapter.

Some VBA needs attention only because the qualifiers you use for the recordset might change when it becomes an external query. Native Access doesn't have pass-through queries 'cause there is nothing to pass to. Some queries can be sequential-only. Some MUST be sequential-only. Things like that will have to be considered.

To the good, you can implement some nice features on your DB server that are much more difficult to implement in native Access - like detailed audit logs, data triggers, detailed roll-forward/roll-back actions... good things that Access, as a small-business tool, simply cannot do. It is just not in the same league as the big kids on the block like ORACLE, SQL Server, DB2, etc.

You'll also make your network folks happy because using a backend database server reduces overall traffic. Access uses a shared disk as a FILE server, not a DB server. That means it has to read EVERY RECORD from ANY PARTICIPATING TABLE. Queries are still done on the workstation. With a backend DB server, you just shoot over the SQL and get back ONLY the result set from that query, not the whole table set.

You'll also see better performance whenever you have "nested" queries since nested queries can be executed on the backend server. Particularly if you can eventually work with your backend DBA to make some of the more complex queries "stored" rather than dynamic.