converting Access database to sql server2000

Hi all
Im new to SQL server and have now spent a couple of weeks trying to convert our companies Access 2000 database to SQL Server 2000. which has about 15 users however more users will be required to use it up to 50. I have been trying to turn my MDB database into ADP however have done tables, forms, reports and views completed and working fine, however i need to update, delete and add etc information in several tables at once, i.e. customer, orders, employee, ect. I used a query in access, i know views work but only with one table at a time. can someone please enlighten me as to what the norm is for using the equivelent of a query to update etc information from several table linked to a form. (Is it a stored procedure or a transact SQL statement?)
Thanks
JimAs far as I know SQL generally, you need to execute one UPDATE per table. You have no forms in SQL Server itself, so you'll need to code the updates in a stored proc.
Maybe someone else knows a better way?|||Cheers Coolberg
I had thought that some long winded code was required to perform this operation, creating stored procedures does seem to be the only option i have. Never mind better get my head in a book and get coding.
It is a bit of a letdown when you diversify from access 2000 MDB to an Access ADP file and you get so far no information is available for creating something similiar to access queries, where you can combine information from several tables onto one form and update, delete etc.
Thanks for your help... if anyone else has any ideas web links etc than id be more than greatful
Jim|||Hello James,

I am not 100% sure I am following everything you are talking about but you can easily move data from Access to SQL Server using a DTS package and more specifically the DTS Designer. This is a lot easier than writing the transact sql unless you are just doing SELECT INTO statements. Here is a link to get you started:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dtssql/dts_tools_des_4fg2.asp

I also believe that you are referring to preserving the parent/child relationships that are displayed by using a form. However, if you are just replicating the data then the relationships will remain if you include all the keys.

Finally, if you are asking how to mimic the forms in Access in SQL Server then this is only done through another language, be it VB / C++ / C# / ASP / Perl / Java, ect...

I hope that helps point you in the right direction.|||Hi Krusty
Thanks for the info on DTS package have just used it. It did transfer tables etc. However much of this was accomplished when I used the upsize wizard to convert form Access to SQL. Hasnt really helped with the complicated queries involving several joins (which were original part of my Access database linked to my main input form). Think Im going to have to compare Access and SQL server Delimiters, Constants and Operators within my Access queries and change them accordingly then try it. Will be a lot of work but hopefully worth it.

I also believe that you are referring to preserving the parent/child relationships that are displayed by using a form. However, if you are just replicating the data then the relationships will remain if you include all the keys. This is correct the tables and relationships within my SQL server database linked to ADP are fine, what the problem is if I can hopefully explain it briefly, I want SQL server 2000 to somehow work with several tables at once linked to a main form that users can update, delete, create new records etc which is how Access works using a query (with several tables within that query linked to a form) I know that queries dont exist within SQL server and want to use something similar.

I did consider linking my Access.mdb with SQL using ODBC however this would not improve Performance and scalability of my database application, therefore this is the easiest option but not a viable one for me.

Finally, if you are asking how to mimic the forms in Access in SQL Server then this is only done through another language, be it VB / C++ / C# / ASP / Perl / Java, ect... I do intend to reproduce the forms reports etc in Visual Basic 6 i.e. the parts on the clients machine, however my intention is to get the application working with Access ADP first as this is much simpler, this will be done at a later stage.
Thanks for your help Krusty
Jim|||James,

Ok I think I now understand what it is you are trying to do. This can be accomplished in a single query using transaction flow, let me explain.

Lets say you have a customer table and an address table. The address table uses the customer ID as a foreign key to connect the two tables. Here is a query that you could use to insert those records.

BEGIN TRAN
INSERT INTO CUSTOMER (NAME) VALUES (@.NAME)
IF @.@.IDENTITY IS NOT NULL
INSERT INTO ADDRESS (ADDRESS, CUSTOMERID) VALUES (@.ADDRESS, @.@.IDENTITY)
IF @.@.IDENTITY IS NULL
ROLLBACK TRAN
ELSE
COMMIT TRAN
GO

One the query assumes that you are using an identity field as the primary key. The @.@.IDENTITY function will return NULL if the insert fails but will return the value of the identity field if the insert were successful. This is not a statement that I would use in production but more or less give you an idea of how to accomplish inserting related data. Updates work very similar. Check out the Global functions to help you along the way they all start with @.@..

HTH!

PS: One thing you will have to change in your current queries from Access to SQL Server is changing " to ' for string values. Access uses a double quote where SQL Server uses a single quote.|||Thanks Krusty,wasnt quite sure what you meant by this code, maybe i need to get a bit more advanced on the subject.
However found what i needed! after surfing the web for hours.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql7/html/access2sql.asp

Basically for anyone else stuck on this problem it says:
Migrating Microsoft Access Queries
You must move your existing Access queries into SQL Server in one of these formats:

Transact-SQL scripts
Transact-SQL statements are usually called from database programs, but you can use SQL Server Query Analyzer, included in SQL Server 7.0, to run them against the database directly. SQL Server Query Analyzer helps developers to test Transact-SQL statements against development databases, or to run Transact-SQL statements that perform queries, data manipulation (INSERT, UPDATE, DELETE), or data definition (CREATE TABLE).

Stored procedures
Developers can move most Transact-SQL statements that originate from Access queries (SELECT, INSERT, UPDATE, and DELETE) into stored procedures. Stored procedures written in Transact-SQL can be used to encapsulate and standardize your data access, and are actually stored within the database. Stored procedures can run with or without parameters and are called from database programs or manually from SQL Server Query Analyzer.

Views
Views are used as virtual tables that expose specific rows and columns from one or more tables. They allow users to create queries without directly implementing the complex joins that underlie the query. Views do not support the use of parameters. Views that join more than one table cannot be modified using INSERT, UPDATE, or DELETE statements. Views are called from Transact-SQL statements, and can also be used in *.scripts that are run in SQL Server Query Analyzer. SQL Server views and the SQL-92 standard do not support ORDER BY clauses in views.

Thanks all|||I'm in a similar position migrating complex Access queries to SQLServer2000.

I'm intending to code all the subordinate queries/views and to encapsulate these within a stored procedure that feeds user parameters to the relevant subordinate views.

However, using "View Designer" seems fine until you get to trying to use user-entered parameters - is there any way around this? ie. How can you introduce parameters into views?

Hoping someone can help me here.
Regards, Alex|||This only applies to the data itself:

I just did this. I used Access to export to SQL. Very easy to do and
if you want, I can send you my procedures. Oh heck, let me go and
find it and post it below..........

While using Access, export to SQL by:

1) Save as type: ODBC Database()
2) Export table to: same name CTR-C to copy name to buffer (youll need this later).
3) DSN: CTR-V, New
4) Data Source: SQL Server
5) Data Source Name: CTR-V, Next, Finish
6) Description: CTR-V
7) Server: Local (from drop down)
8) Authentication: NT
9) Default Database: yourdatabase (and not the default master)

That's it. Be sure to give permissions within SQL to tables etc.|||Updating of multiple tables in view can be done by instead of update trigger on view.
This trigger is run instead of operation and that is why it can be used to extract updates for single tables
from virtual inserted table.
See my post http://dbforums.com/showthread.php?threadid=640545
and BOL for more information about triggers (topic "CREATE TRIGGER").|||Bobby: Thanks for your response, I had problems exporting, but managed to solve my problems by using UDFs instead of Views which cannot accept any parameters. Essentially what I now have is a series of linked dependent UDFs (which generate their respective temp.tables), and then a stored procedure at the top level, which in turn communicates with my Coldfusion CFC (& then to Flash MX via Flash Remoting).
THis now seems to work fine!

Thanks again (also much indebted to Isapleny!).
Alex|||I'm glad to hear it's working. Have a great weekend (which is already
started for you).

BobbyJ