Manipulate Access Database from Web Page

We use an Access database on our website to maintain member and event information. All pages on the website are created/maintained using Dreamweaver CS4. I need to do the following from a web page when requested:
1. Lock affected tables from user update while the following is executed:
    a. Delete all records in three different tables.
    b. Run three queries to extract information from a member table and add them to the three tables.
    c. Delete records from the member table.

I've tried a couple of approaches and researched how to do this, but with no success. Using Dreamweaver, do I need to create record sets or just open a connection and execute the SQL?
Can I include the connection string or does it need to be redefined in the web page? As you can see, I'm floundering.
slegyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeffrey CoachmanMIS LiasonCommented:
How long do those steps take to run?
To me, it might be simpler to just do those steps over-nite (or some other low volume time), ...and just make the site inaccessible for those few minutes (put up a "offline" messages similar to what this site does)
0
slegyAuthor Commented:
This is very low volume. The overnight solution would not be acceptable to the user.
0
Jeffrey CoachmanMIS LiasonCommented:
<This is very low volume. >
OK, I also suggested doing this during a low volume period... (ex lunchtime when everyone is at lunch)


But if this really needs to happen dynamically then lets wait for a web expert to chime in.
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

slegyAuthor Commented:
And even if this were acceptable, I'm still looking for the appropriate coding technique to do it.
0
Jason C. LevineNo oneCommented:
Dreamweaver used to be able to connect directly to Access databases via its server behaviors for ASP Classic but that was a long, long time ago before people stopped using Access to power a production web site (it's not a pleasant thing to do for a variety of reasons).  My guess is to try it with the built-in behaviors in CS4 and see if the support is still there.

do I need to create record sets or just open a connection and execute the SQL?

You don't need to create recordsets to delete records.  The delete record behavior deletes the ID passed to it.  Or you can force the SQL through, it really is all the same thing at this point.

Can I include the connection string or does it need to be redefined in the web page?

If you work with Dreamweaver behaviors you will create a connection file that gets called as an include whenever you invoke a server behavior.  That file will have the db connection strings.

You didn't ask, but I will provide a workaround.  Because Access is...less than ideal...for a production web site, is there any way to move the data to an MS or MySQL server and convert Access to a front-end solution?  Moving the data to a real database server would give you the full range of options in Dreamweaver and also handle the locking issues in a saner way.
0
slegyAuthor Commented:
I have no problems with Dreamweaver connecting to the Access database. The site has been running without issue for over 5 years.

This was not at the core of my question, but I will add this. When we made the decision to use Access, it was because the local office for the organization needs a local database to support its day-to-day operations. This was most easily accomplished with Access. From the local office, only the information needed on the website is uploaded. This has been a quandary from day one - how to work effectively both locally and online. If there is a workable solution for this, I would love to be educated.

We are now contemplating changes that would require data to be downloaded, which is behind the question about deleting and inserting records.

"You don't need to create recordsets to delete records.  The delete record behavior deletes the ID passed to it.  Or you can force the SQL through, it really is all the same thing at this point." -- I have no problem deleting or inserting individual records based an ID. What I  want to do is run queries that would delete all the records in a table - then run queries that would select records from a table and insert them in another table.

Yes, the connection strings exist and are operational.

We are just about to embark on an evaluation of the entire process and certainly converting to MySQL is one of the considerations - if a solution can be found for support at the local level. It may be too pricey to consider, but even if that direction is chosen, my guess is that it would take at least a year. In the meantime, I will need a solution to this problem.

I found something akin to the following code to accomplish what I am trying to do. It doesn't work, but I would like to ask if this is the approach I should be using and if other examples can be provided that would help solve the problem.

<!--#include file="../Connections/eventCalendar.asp" -->
<%
 Dim objConn
 Dim objCommand

 Set objConn = Server.CreateObject("ADODB.Connection")
 Set objCommand = Server.CreateObject("ADODB.Command")
 strConn = "AccessConnectionString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|eventCalendar.mdb" providerName="System.Data.OleDb"

 objConn.Open(strConn)
 
 strSQL = "INSERT INTO members_renew SELECT members.* FROM members"

 objCommand = strSQL
 objConn.Execute objCommand

 objConn.Close

 Set objCommand = Nothing
 Set objConn = Nothing
 %>
0
Jason C. LevineNo oneCommented:
From the local office, only the information needed on the website is uploaded. This has been a quandary from day one - how to work effectively both locally and online. If there is a workable solution for this, I would love to be educated.

I run a shop with a similar setup.  When I started there in 1992 we had DBaseIII, then DBaseIV, then Access95, 2000, 2003, and now 2007.  We started with web pages in 1994 but really didn't try to integrate the database until 2000-2001 and made a series of (bad, in hindsight) decisions to only expose part of the data to the web and bring down any changes overnight in a synch process.  To prevent overwrites, we mandate that the data that is exposed to the web can only be edited via the web and we've created admin-only backends to allow the staff to do that.  However, it still means the data entry staff would be working in the browser and in Access simultaneously depending on what exactly they were doing.  I've been spending much of the past two years fixing the last 20 years of stupidity and moving all the databases online and converting access to a front-end/query/reporting tool that will access MySQL via ODBC.  This is the only way I can think of to work effectively on the same data locally and online...one set of data that both the web and MS Access can see, no copying, no synching.

What I  want to do is run queries that would delete all the records in a table - then run queries that would select records from a table and insert them in another table.

This would work, but it would be fairly slow to execute and I would worry about locking, especially if the web side can be triggered again while the queries are executing.

We are just about to embark on an evaluation of the entire process and certainly converting to MySQL is one of the considerations - if a solution can be found for support at the local level.

Or MS-SQL, which works better with Access.  You can also connect Access to MySQL via the MyODBC drivers available from the MySQL web site.  If the MySQL server and web server are locally accessible, this can work pretty well and be reasonable fast.  If the MySQL server and web server are at a service provider, success will depend on bandwith available and certain actions in Access will go slower than expected.

but I would like to ask if this is the approach I should be using and if other examples can be provided that would help solve the problem.

I don't do ASP but in theory it would work as you describe, albeit in a brute-force way. I kind of agree with boag2000 in that the flow would look something like this:

1) Someone initiates the sequence
2) Pages that can affect user data are taken offline somehow (probably temporary redirects to a "This action is unavailble" message).
3) Something happens locally to lock out local edits (n.b. Not sure how this part would work).
4) Run your query scripts
5) Unlock local db
6) Reactivate web stuff
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
slegyAuthor Commented:
Sorry, I didn't realize that this question had been answered in such a clear and detailed manner. Thank you so very much for the time you took to respond. This helps me out immensely and will also be very much appreciated by my client.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Adobe Dreamweaver

From novice to tech pro — start learning today.