Linking access tables on a web server

[shikha]

Hi,

I am back again seeking some advice
Currently, I have one access table, which was being modified by ASP script
on a regular basis, and simultaneously was being queried by customers.
Hence, script was unable to modify the table, because it was being read at
the same time.

Tried upgrading to SQL Server, but our firewall is blocking access to SQL
Server; so I am back again to Access database, and wondering how can I
overcome the read/modify problem.

I thought of creating new table in same database and linking it to original
table. Ths way original table will be modified by scritp, and the new
database will be linked to web page which will be queried upon...not sure if
thats even possible in practical. Becaue when I upload the access database
on the server with two tables linked, i am getting the error.

Any suggestions? any help will be greatly appreciated!!!
Thanks,

Shikha

 

[Ian Haynes]

I am back again seeking some advice

Currently, I have one access table, which was being modified by ASP script
on a regular basis, and simultaneously was being queried by customers.
Hence, script was unable to modify the table, because it was being read at
the same time.

Another approach might be to write to the db using a function which checks
for errors and tries again until it suceeds. I've used this on several
occasions with Access dbs where the can be read and write conflicts.

The code I use is as follows (not my own creation but don't recall where I
found it), where strSQL is the SQL statement and conn is the ADOB
connection.

Dim intTries, blnSuccessful
blnSuccessful = True

Const MAX_TRIES = 10

On Error Resume Next
' We want to try again IF we got an error AND we haven't
' tried the maximum number of times yet
Do
Err.Clear
Conn.Execute (strSQL)
intTries = intTries + 1
Loop While (Err.Number <> 0) And (intTries < MAX_TRIES)

If Err.Number <> 0 Then
' Exceeded the maximum number of tries and was still unsuccessful;
' display an error message
blnSuccessful = False
Response.Write "An error has occurred!<BR>"
End If

This will handle quite heavy read/write scenarios.

HTH

 

[shikha]

Hi Ian,

Thank you so much for the code!!! I am just not sure where to place this
code;
so if you can please let me know!!! And one last thing; this site is being
used by 100 corporate customers, so will this code be able to support that
kind of traffic?

Here is the strcuture of my code which is modifying the table:

<%

Initialzing variables
Requesting variables informaton

'Connection DB
Dim oSQLRecordSet
Dim sSQL_ConnectionString
Dim oSQLConnection

sSQL_ConnectionString = "DRIVER=Microsoft Access Driver (*.mdb);DBQ=" &
Server.MapPath(".mdb")
oSQLRecordSet = Server.CreateObject("ADODB.Recordset")

Set oSQLConnection = CreateObject("ADODB.Connection")
oSQLConnection.Open sSQL_ConnectionString



'Step 3 - Parse Flat File
Dim sSQL_Insert
Dim sSQL_Delete

sSQL_Delete = "delete from table where TroubleNum = '" & sTroublereportid &
"'"

sSQL_Insert = "insert into ISP values ("


oSQLConnection.execute sSQL_Delete


response.write(sSQL_Insert)
oSQLConnection.execute sSQL_Insert

%>

 

[shikha]

Hi Ian,

Thank you so much for the code; I am just not sure where to place this code!
Here is the general structure of ASP script which is modifying the database:

<%
Variables initialized

'Connection DB
Dim oSQLRecordSet
Dim sSQL_ConnectionString
Dim oSQLConnection

sSQL_ConnectionString = "DRIVER=Microsoft Access Driver (*.mdb);DBQ=" &
Server.MapPath(".mdb")
oSQLRecordSet = Server.CreateObject("ADODB.Recordset")

Set oSQLConnection = CreateObject("ADODB.Connection")
oSQLConnection.Open sSQL_ConnectionString



'Step 3 - Parse Flat File
Dim sSQL_Insert
Dim sSQL_Delete

sSQL_Delete = "delete from Table where TroubleNum = '" & sTroublereportid &
"'"

sSQL_Insert = "insert into Table values (" different if else conditions)


oSQLConnection.execute sSQL_Delete


response.write(sSQL_Insert)
oSQLConnection.execute sSQL_Insert

%>

Thanks once again for your help!!!

Shikha

 

[Ian Haynes]

Thank you so much for the code!!! I am just not sure where to place this

code;
so if you can please let me know!!! And one last thing; this site is
being
used by 100 corporate customers, so will this code be able to support that
kind of traffic?

Given that they aren't all reading and writing at the same millisecond in
time (or even second) it should handle that number of users, but only
testing will tell for sure.

Given you have two processes going on, Insert and Delete, it will be simpler
to put the code into a subroutine, which takes the SQL and Connection as
inputs.

-----------------------------------
Sub ModifyDB(conn, strSQL)

Dim intTries, blnSuccessful
blnSuccessful = True

Const MAX_TRIES = 10

On Error Resume Next
' We want to try again IF we got an error AND we haven't
' tried the maximum number of times yet
Do
Err.Clear
Conn.Execute (strSQL)
intTries = intTries + 1
Loop While (Err.Number <> 0) And (intTries < MAX_TRIES)

If Err.Number <> 0 Then
' Exceeded the maximum number of tries and was still unsuccessful;
' display an error message
blnSuccessful = False
Response.Write "An error has occurred!<BR>"
End If

End Sub
----------------------------------

Then call the Sub where you want to Delete or Insert

Instead of
oSQLConnection.execute sSQL_Delete

Use

ModifyDB oSQLConnection, sSQL_Delete

Instead of
oSQLConnection.execute sSQL_Insert

Use

ModifyDB oSQLConnection, sSQL_Insert


Hope that makes sense.

 

[Stefan B Rusynko]

In your existing code replace the 3 last lines:

oSQLConnection.execute sSQL_Delete
response.write(sSQL_Insert)
oSQLConnection.execute sSQL_Insert

With all the below

ModifyDB oSQLConnection, sSQL_Delete
ModifyDB oSQLConnection, sSQL_Insert

Sub ModifyDB(conn, strSQL)
Dim intTries, blnSuccessful
blnSuccessful = True
Const MAX_TRIES = 10
On Error Resume Next
' We want to try again IF we got an error AND we haven't tried the maximum number of times yet
Do
Err.Clear
Conn.Execute (strSQL)
intTries = intTries + 1
Loop While (Err.Number <> 0) And (intTries < MAX_TRIES)
If Err.Number <> 0 Then
' Exceeded the maximum number of tries and was still unsuccessful; display an error message
blnSuccessful = False
Response.Write "An error has occurred!<BR>"
End If
End Sub



_____________________________________________
SBR @ ENJOY (-: [ Microsoft MVP - FrontPage ]
"Warning - Using the F1 Key will not break anything!" (-;
To find the best Newsgroup for FrontPage support see:
http://www.net-sites.com/sitebuilder/newsgroups.asp
_____________________________________________


| Hi Ian,
|
| Thank you so much for the code; I am just not sure where to place this code!
| Here is the general structure of ASP script which is modifying the database:
|
| <%
| Variables initialized
|
| 'Connection DB
| Dim oSQLRecordSet
| Dim sSQL_ConnectionString
| Dim oSQLConnection
|
| sSQL_ConnectionString = "DRIVER=Microsoft Access Driver (*.mdb);DBQ=" &
| Server.MapPath(".mdb")
| oSQLRecordSet = Server.CreateObject("ADODB.Recordset")
|
| Set oSQLConnection = CreateObject("ADODB.Connection")
| oSQLConnection.Open sSQL_ConnectionString
|
|
|
| 'Step 3 - Parse Flat File
| Dim sSQL_Insert
| Dim sSQL_Delete
|
| sSQL_Delete = "delete from Table where TroubleNum = '" & sTroublereportid &
| "'"
|
| sSQL_Insert = "insert into Table values (" different if else conditions)
|
|
| oSQLConnection.execute sSQL_Delete
|
|
| response.write(sSQL_Insert)
| oSQLConnection.execute sSQL_Insert
|
| %>
|
| Thanks once again for your help!!!
|
| Shikha
|
| "Ian Haynes" wrote:
|
| > > I am back again seeking some advice
| > > Currently, I have one access table, which was being modified by ASP script
| > > on a regular basis, and simultaneously was being queried by customers.
| > > Hence, script was unable to modify the table, because it was being read at
| > > the same time.
| > >
| >
| > Another approach might be to write to the db using a function which checks
| > for errors and tries again until it suceeds. I've used this on several
| > occasions with Access dbs where the can be read and write conflicts.
| >
| > The code I use is as follows (not my own creation but don't recall where I
| > found it), where strSQL is the SQL statement and conn is the ADOB
| > connection.
| >
| > Dim intTries, blnSuccessful
| > blnSuccessful = True
| >
| > Const MAX_TRIES = 10
| >
| > On Error Resume Next
| > ' We want to try again IF we got an error AND we haven't
| > ' tried the maximum number of times yet
| > Do
| > Err.Clear
| > Conn.Execute (strSQL)
| > intTries = intTries + 1
| > Loop While (Err.Number <> 0) And (intTries < MAX_TRIES)
| >
| > If Err.Number <> 0 Then
| > ' Exceeded the maximum number of tries and was still unsuccessful;
| > ' display an error message
| > blnSuccessful = False
| > Response.Write "An error has occurred!<BR>"
| > End If
| >
| > This will handle quite heavy read/write scenarios.
| >
| > HTH
| >
| >
| > --
| > Ian Haynes
| > MS MVP FrontPage
| > http://www.ew-resource.co.uk
| >