Upsizing Ms Access to SQL Help

Access Upsizing

[Taffman]

I've sucessfully managed to upsize my Ms Access Database to SQL Server by creating a .ado application file. This give me an Ms Access client file with tables linked to the SQL database.

so far so good, I now import all my form, modules, macro, reports etc from the old .mbb file but I cant find a way to import QUERIES.

I assume the queries or Views must be uploaded abd converted to SQL database also, how do i do this?

[oxbaker]

queries are not STORED in SQL Databases Queries are natural language "searches" that you do ON sql tables, etc. that being said you can create VIEWS in SQL and just copy and paste the query statment to build the view.

But the queries should stay w/ access and you run them on the NEW tables in the DB.

hth,
mcm

[Taffman]

Many thanks for this, as you proberbly guess I an SQL newbie :-)

There appears to be several types of querie i.e.:

Query
Stored Procedure
Text Stored Procedure
Text Scalar Function
Text Variable-Value Function.

All seem pretty simular to set up however the obvious choice, the "Query" doesn't appear to have a SQL query view whereas the others do.

Should I paste my SQL query into a Stored Procedure?

[oxbaker]

not really. The Business Layer should be what calls the data .

example:

you have an app (written in access) this is your BUSINESS LAYER

you now have a database in SQL its your DATA LAYER

the data layer should remain as stable and "granular" as possible, with nothing more than tables, views, stored procs. That being said Stored procedures are not always the best way to go and there is an ongoing debate about the benefits of using SQL from the Business Layer as opposed to using stored procedures.

I think from your questioning that you dont really grasp how queries should be run. Lets say you have a form, and it's data source is a query. That query will just point to the database and pull all the necessary information that it needs to display the data on the form. That being said, since you desing your form in the business layer, logic dictates that you probably want to keep your QUERY in the Business Layer, so if you modify the forum and have to get additional (or different) data you can modify the query without having to go to the SQL Server and add a new stored procedure.

So to clarify.

The database really should have just the tables in it. IF there were Stored Procedure (which are usually reserved for asynchronis data manipulation) then they should be moved, but just queries to create data sets for forums and reports should stay in access and just point to the tables they need in the SQL DB. (im assuming the tables all have the same names)

hth,
mcm