Communicating With SQL Server

[steve2507]

Hi,

As you can see I'm new here and I'm hoping you can help.

I am in the process of developing an order management system for our website. Where orders are downloaded, processed automatically etc and have hit a stumbling block.

Building the database itself isn't a problem, the problem is getting the access db to comunicate with the MSSQL database on our site server.

We need Access to download orders change values etc. Here are the details of what we need from the communication:

Orders to be downloaded
Tables - orders, oitems, customers
If customer doesn't exist then new record created, if does exist then is updated.
New order and oitems created.
Download should be on the following criteria and available separately. Also needed would be the ability to limit the last order number downloaded, so that if a customer was currently paying and they hadn’t completed yet, their order wasn’t marked as failed.
• If a payment has been confirmed - when these are downloaded the oprocessed value would be changed to True
• If no payment is confirmed - when these are downloaded the oprocessed value would be changed to True and the ocardtype value changed to Fail


After orders are processed the system will determine what products will need to be hidden. This will generate a query which needs to be sent to MSSQL which will hide products and/or product variants by making changes in the products table.
When an order has been dispatched the tracking number is usually emailed to the customer via the VP tracking we need to determine if we can do this via the access database and for it to be uploaded to MSSQL.
There are various tracking messages we send, depending on how an order is sent, if it is an exchange, to follow etc.
The ostatus of the order needs to be amended on shipment.
After delivery from suppliers some items need to be displayed again. As with when products are hidden after order processing a query would be created which would change the hide field for products that can now be shown again.

Sorry if this sounds like rubbish, but effectively we need the Access database to talk to, check and download orders from the SQL Server.

BUT

I am really stuck as to where to start.

Any guidance or pointers would be greatly appreciated.

Thanks

Steve

[June7]

Is this what you need http://office.microsoft.com/en-us/ac...005274586.aspx

[steve2507]

Thanks for the reply.

I've tried that approach but really we could do with having the orders downloadable as having to connect via the internet is going to really cause speed issues.

[June7]

Sorry, I didn't catch onto the internet requirement. Never done that. Maybe this one helps http://msdn.microsoft.com/en-us/library/ms175483.aspx

If your db were Access instead of SQL, guess Sharepoint would be an option.

This is not really an Access question, it is web design.

[steve2507]

Sorry, I didn't catch onto the internet requirement. Never done that. Maybe this one helps http://msdn.microsoft.com/en-us/library/ms175483.aspx

If your db were Access instead of SQL, guess Sharepoint would be an option.

This is not really an Access question, it is web design.

Thanks for the reply again.

It's a mixture of all three, the SQL Server is on our webserver, whilst the Access database is on our office network and we are trying to get the Access db to talk to and update the SQL Server.

I think this is a big part of the problem of getting help with a solution as it crosses all the disciplines.

[June7]

Connecting Access and SQL Server over a network should be easy. It is the web interface you want that is tricky. And sorry, I don't have experience in data driven web pages.

I expect need some web programming software like ASP, ASP.net, PHP. Some of these forums might be more helpful http://forums.aspfree.com/

[steve2507]

Connecting Access and SQL Server over a network should be easy. It is the web interface you want that is tricky. And sorry, I don't have experience in data driven web pages.

I expect need some web programming software like ASP, ASP.net, PHP. Some of these forums might be more helpful http://forums.aspfree.com/

Thanks for the advice, I'll pu the same question up there.