Problems with converting Access database to SQL

[havoc33]

I'm converting a Access database to SQl, but I've ran into some problems. I'm a complete rookie at this and I'm trying out a free shopping cart from

http://www.urlogy.com/.

When converting to SQL, it says on the website something like this:

"Remember to also create the stored procedures with the correct parameters (check the Access Queries)".

There are 4 Access queries that don't make the conversion. I'm a bit lost here, any help?

 

[MakeItSo]

Hi havoc,

can you provide more info, please? What type of queries are these? Select, Insert, Update, Delete, Union...?
Perhaps you can also post one or two of those that didn't make it?

Cheers,
Andy

[blue]The last voice we will hear before the world explodes will be that of an expert saying:
"This is technically impossible!" - Sir Peter Ustinov[/blue]
andreas.galambos@bowneglobal.de
HP:

http://andygalambos.de

 

[havoc33]

All the tables from the Access database converts fine into the SQL database. But the access database has four stored queries that don't convert. Well, actually when I use the DTS wizards to convert the database, one of the queries, "qryOrders", is listed as a "view" and get's converted to a table.

But DTS wizard doesn't touch the other three. So when I populate the tables and try using the shopping cart site, I get a error msg when trying to enter the products on the site. Obviously, the error msg states that one of the queries cannot be found.

On the website for the shoppingcart it says the following about converting to SQL:

"What if I want to use SQL server instead of Access?

If you are able to convert the Access database to Microsoft SQL server, the code should work fine. Remember to also create the stored procedures with the correct parameters (check the Access Queries).
Be sure to also change the connection string, so that it refers to a SQL server driver or provider instead of Microsoft Access driver."

So how do I get this to work? Thanks for taking the time to help me..

 

[MakeItSo]

Concerning the 3 queries:
As I mentioned, depends on their type.
You ought to post their SQL statement (Open Access, open the query in query designer, switch to SQL view, mark all and copy)

Concerning the connection string:
Check your ASP.
You should find a Connection String in its code, that will look similar to this:

DRIVER={Microsoft Access Driver (*.mdb)};DBQ=URL=X:\MyMDB.mdb"

You need to change it to

"Driver={SQL Server}; Server=MyServerName; Database=yourSQLDB;" & _
           "Uid=yourUsername; Pwd=yourPassword"

P.S: For the latter, you might also need help from the ASP forum forum333

Good luck,
Andy

[blue]The last voice we will hear before the world explodes will be that of an expert saying:
"This is technically impossible!" - Sir Peter Ustinov[/blue]
andreas.galambos@bowneglobal.de
HP:

http://andygalambos.de

 

[havoc33]

Yeah, I understood the connection part.

Here is the four queries:

qryOrderInfo


SELECT orders.orderID, orders.*, products.ccode, products.cname, products.cprice, oitems.numitems,
customers.*
FROM customers INNER JOIN (orders INNER JOIN (products INNER JOIN oitems ON
products.catalogID = oitems.catalogid) ON orders.orderID = oitems.orderid) ON customers.custID =
orders.ocustomerid


qryOrders


SELECT products.*, orders.*, oitems.*, customers.*
FROM customers INNER JOIN (orders INNER JOIN (products INNER JOIN oitems ON
products.catalogID = oitems.catalogid) ON orders.orderID = oitems.orderid) ON customers.custID =


qryProdsCategory


SELECT products.*, products.ccategory, categories.catdescription
FROM categories INNER JOIN products ON categories.categoryID = products.ccategory
WHERE (((products.ccategory)=[theCategory]));


qryProduct


SELECT products.*, products.catalogID, products.catalogID AS shownID
FROM products
WHERE (((products.catalogID)=[prodID]));

 

[MakeItSo]

These are all rather simple SELECT queries, which can normally simply be "translated" into views.

I assume, the queries which were not accepted were
qryOrders, qryProdsCategory and qryProduct:

This is very probably caused by
a) qryOrders being incomplete:

 ON customers.custID =

If the query ends here, it won't run anyways, since the join is incomplete.

b) The WHERE clause of the other two queries contain references to an unknown field:

WHERE (((products.ccategory)=[b][theCategory][/b]))

and

WHERE (((products.catalogID)=[b][prodID][/b]));

What are "theCategory" and "prodID" ?
They don't seem to be valid table fields, thus these queries won't run either and thus aren't converted.

Hope this helps,
Andy

 

[havoc33]

No, the only query accepted was qryOrders...

 

[havoc33]

Maybe you could innstall that shopping cart yourself, and have a look? It only takes a couple of minutes. The url is:

http://www.urlogy.com/asp/ashopkart.asp

Once again, I really appreciate your help here.

 

[MakeItSo]

I have taken a look at the database:
In Access format, it seems OK.
However, converting to SQLServer really does produce problems:
- tables convert fine
- queries don't.

qryOrders gets imported - but as a table, not as a view.
The other queries do not get imported.
Reason: see two posts above. I tried to manually "import" the queries by
- creating a new, empty view
- pasting the query statement
- savin as "qry..."

However I get error messages like "[oid] not a valid column name" and so on. [oid], [prodid] and [theCategory] seem to be textboxes on the asp page / variables. Obviously, SQLServer does not allow this within views and so these views can't be created.

As you said, you are a rookie, I'd recommend you stick with the database in Access format for the beginning, and rather setup your own SQLServer DB clean from scratch by the way.

Any proposals from the SQL cracks of this forum?

Cheers,
Andy

[blue]The last voice we will hear before the world explodes will be that of an expert saying:
"This is technically impossible!" - Sir Peter Ustinov[/blue]
andreas.galambos@bowneglobal.de
HP:

http://andygalambos.de

 

[havoc33]

Hi Andy! Believe it or not, the shopping cart is now up and running! I entered the queries like this:

Create Procedure qryOrderInfo
as
SELECT o.orderID, o.*, p.ccode, p.cname, p.cprice, oi.numitems, c.*
FROM customers c
INNER JOIN orders o ON c.custID = o.ocustomerid
INNER JOIN oitems oi ON o.orderID = oi.orderid
INNER JOIN products p ON oi.catalogID = p.catalogid



Create Procedure qryOrders
as
SELECT p.*, o.*, oi.*, c.*
FROM customers c
INNER JOIN orders o ON c.custID = o.ocustomerid
INNER JOIN oitems oi ON o.orderID = oi.orderid
INNER JOIN products p ON oi.catalogID = p.catalogid



Create Procedure qryProdsCategory
@theCategory as nvarchar(200)
as
SELECT p.*, p.ccategory, c.catdescription
FROM categories c
INNER JOIN products p
ON c.categoryID = p.ccategory
WHERE p.ccategory=@theCategory


Create Procedure qryProduct
@prodID as int
as
SELECT *, catalogID, catalogID AS shownID
FROM products
WHERE catalogID=@prodID

Then I just had to chance the datatype in one (i think) of the colums in the orders table, and everything worked like a charm. Anyway, thanks for all the help!