Converting an Access database to a web-based format.

[RCS]

Okay, a rather 'interesting' situation has arisen at a place I work: I need
to convert a database from Access to something that can be used over the
web.

I am currently maintaining and developing a mid-sized Access database (60
tables, 25 simultaneous users).
Although I've been tossing up if I should try to convert the back end to SQL
server (We have a license), the performance has been good enough that it
always seemed a waste of time, so it's stayed as an MDB, split into front
and back end.

Due to recent changes in situation, certain individuals have decided the
entire database needs to be put on the web.

Unfortunately, I got stuck with the job... and it was all decided before
anyone bothered to see what it actually involved.
Basically, I was told to take an Access database and make it all webby...
And now I'm pretty much stuck for where to look next.

I know the back end it going to have to be SQL. Converting the tables
shouldn't be too much of a problem.

But the front end is where I run in to trouble- obviously a web browser will
be used, but I'm going to need something to generate the HTML pages. Last
time I looked in to it, you had to learn something like VBscript or CGI and
do it all manually.
I'm hoping there are better tools around now. (I've really gotten to like
the form editor on Access! )

While I doubt there will be anything that can convert any of the work that's
been done (Forms and reports), I was wondering if anyone had any
reccomendations on how to get started on this kind of project- because in
all honesty I'd never even considered it before now....

[Steve Jorgensen]

On Wed, 7 Jan 2004 10:49:34 +0800, "RCS" <robsol{at}tower{DOT}ne{DOT}au>
wrote:

Okay, a rather 'interesting' situation has arisen at a place I work: I need
to convert a database from Access to something that can be used over the
web.

I am currently maintaining and developing a mid-sized Access database (60
tables, 25 simultaneous users).
Although I've been tossing up if I should try to convert the back end to SQL
server (We have a license), the performance has been good enough that it
always seemed a waste of time, so it's stayed as an MDB, split into front
and back end.

Due to recent changes in situation, certain individuals have decided the
entire database needs to be put on the web.

Unfortunately, I got stuck with the job... and it was all decided before
anyone bothered to see what it actually involved.
Basically, I was told to take an Access database and make it all webby...
And now I'm pretty much stuck for where to look next.

I know the back end it going to have to be SQL. Converting the tables
shouldn't be too much of a problem.

But the front end is where I run in to trouble- obviously a web browser will
be used, but I'm going to need something to generate the HTML pages. Last
time I looked in to it, you had to learn something like VBscript or CGI and
do it all manually.
I'm hoping there are better tools around now. (I've really gotten to like
the form editor on Access! )

While I doubt there will be anything that can convert any of the work that's
been done (Forms and reports), I was wondering if anyone had any
reccomendations on how to get started on this kind of project- because in
all honesty I'd never even considered it before now....

I'm no expert, but I know enough to tell you that...

A. You're right, you can't just convert your existing Access app directly to
a Web app.

B. There are indeed better tools available than there used to be.

Possible tools to look into are ASP.NET (using Visual Studio .NET or Web
Matrix), Java (ColdFusion, STRUTS, etc.), Zope (Python), and others too
numerous to list. I understand that Web front-end design is very different
than Desktop front-end design, so you should definitely read up on both Web
application design and on the specific tool you choose before diving in.

[Tom van Stiphout]

On Wed, 7 Jan 2004 10:49:34 +0800, "RCS"
<robsol{at}tower{DOT}ne{DOT}au> wrote:

For you to rewrite this app as an ASP app in VBScript would be
impossible - at least as your first web project. Unless you have A LOT
of time and can learn as you go, at least a couple of months.

If this is an intranet solution (i.e. the only access is from inside
the company), you may want to look into the capabilities all recent
Office apps have to be delivered in a browser. This requires IE,
Access on every desktop, and a fast LAN connection.

Sometimes a hybrid solution is possible, whereby the office continues
to use the Access app, perhaps with SQL back-end, and some web users
(perhaps some clients?) use a limited set of web forms (e.g. view
status of my orders, view account balance, submit new order, ...)

If you have just a few web users, you may also want to consider
Windows Terminal Services.

-Tom.

Okay, a rather 'interesting' situation has arisen at a place I work: I need
to convert a database from Access to something that can be used over the
web.

I am currently maintaining and developing a mid-sized Access database (60
tables, 25 simultaneous users).
Although I've been tossing up if I should try to convert the back end to SQL
server (We have a license), the performance has been good enough that it
always seemed a waste of time, so it's stayed as an MDB, split into front
and back end.

Due to recent changes in situation, certain individuals have decided the
entire database needs to be put on the web.

Unfortunately, I got stuck with the job... and it was all decided before
anyone bothered to see what it actually involved.
Basically, I was told to take an Access database and make it all webby...
And now I'm pretty much stuck for where to look next.

I know the back end it going to have to be SQL. Converting the tables
shouldn't be too much of a problem.

But the front end is where I run in to trouble- obviously a web browser will
be used, but I'm going to need something to generate the HTML pages. Last
time I looked in to it, you had to learn something like VBscript or CGI and
do it all manually.
I'm hoping there are better tools around now. (I've really gotten to like
the form editor on Access! )

While I doubt there will be anything that can convert any of the work that's
been done (Forms and reports), I was wondering if anyone had any
reccomendations on how to get started on this kind of project- because in
all honesty I'd never even considered it before now....

[Roger]

with implementing a web version you also loose some of the nice
features that Access gives you by default

I've not used this 'template' strategy yet, but it might give you
ideas
http://www.tonymarston.net/php-mysql...seobjects.html
http://www.tonymarston.net/php-mysql...eobjects2.html
http://www.tonymarston.net/php-mysql...plication.html

mysql is a DBMS, but php can interface to Access too, so you shouldn't
have to convert the data

"RCS" <robsol{at}tower{DOT}ne{DOT}au> wrote in message news:<3f**********************@freenews.iinet.net. au>...

Okay, a rather 'interesting' situation has arisen at a place I work: I need
to convert a database from Access to something that can be used over the
web.

I am currently maintaining and developing a mid-sized Access database (60
tables, 25 simultaneous users).
Although I've been tossing up if I should try to convert the back end to SQL
server (We have a license), the performance has been good enough that it
always seemed a waste of time, so it's stayed as an MDB, split into front
and back end.

Due to recent changes in situation, certain individuals have decided the
entire database needs to be put on the web.

Unfortunately, I got stuck with the job... and it was all decided before
anyone bothered to see what it actually involved.
Basically, I was told to take an Access database and make it all webby...
And now I'm pretty much stuck for where to look next.

I know the back end it going to have to be SQL. Converting the tables
shouldn't be too much of a problem.

But the front end is where I run in to trouble- obviously a web browser will
be used, but I'm going to need something to generate the HTML pages. Last
time I looked in to it, you had to learn something like VBscript or CGI and
do it all manually.
I'm hoping there are better tools around now. (I've really gotten to like
the form editor on Access! )

While I doubt there will be anything that can convert any of the work that's
been done (Forms and reports), I was wondering if anyone had any
reccomendations on how to get started on this kind of project- because in
all honesty I'd never even considered it before now....

[Danny J. Lesandrini]

I've posted this before (recently, even) and I've gotten some good feedback
offline from people who have tried out the code. If you are completely new
to ASP, then maybe it will be too much information, but hey, it works.

Also, the idea of going to ASP .Net is a good one. May be easier than this
traditional ASP code in the long run. All the same, here it is:

As an example of what can be done, go to a site I made for a friend ...
http://www.amazecreations.com/survey/Login.asp

To login, select Admin (all) as the region, Danny Lesandrini as the user and
123456 for a password. This page (and subsequent pages) look up data in
Access tables to create the drop down lists. I even use them in updateable
fields. Click the Edit Tables link and click on Offices for an example.

If this fits your needs, I'll forward the code to you, though I didn't
write it with the idea of anyone looking at it, and comments may be sparce.

You can also check out these articles at my site.
http://www.amazecreations.com/datafa...m&Article=true
http://www.amazecreations.com/datafa...m&Article=true
--

Danny J. Lesandrini
dl*********@hotmail.com
http://amazecreations.com/datafast
"RCS" <robsol{at}tower{DOT}ne{DOT}au> wrote ...

Okay, a rather 'interesting' situation has arisen at a place I work: I need
to convert a database from Access to something that can be used over the
web.

I am currently maintaining and developing a mid-sized Access database (60
tables, 25 simultaneous users).
Although I've been tossing up if I should try to convert the back end to SQL
server (We have a license), the performance has been good enough that it
always seemed a waste of time, so it's stayed as an MDB, split into front
and back end.

Due to recent changes in situation, certain individuals have decided the
entire database needs to be put on the web.

Unfortunately, I got stuck with the job... and it was all decided before
anyone bothered to see what it actually involved.
Basically, I was told to take an Access database and make it all webby...
And now I'm pretty much stuck for where to look next.

I know the back end it going to have to be SQL. Converting the tables
shouldn't be too much of a problem.

But the front end is where I run in to trouble- obviously a web browser will
be used, but I'm going to need something to generate the HTML pages. Last
time I looked in to it, you had to learn something like VBscript or CGI and
do it all manually.
I'm hoping there are better tools around now. (I've really gotten to like
the form editor on Access! )

While I doubt there will be anything that can convert any of the work that's
been done (Forms and reports), I was wondering if anyone had any
reccomendations on how to get started on this kind of project- because in
all honesty I'd never even considered it before now....

[Danny J. Lesandrini]

FWIW, the code below is what I use to generate ASP page shells for me.
It's not perfect, but it does a lot of the typing and generates an FETCH,
INSERT and UPDATE function, as well as drawing the text boxes.

Watch for line wrap. If it doesn't come through, write me at the email
below and I'll send it to you in a zipped mdb format.
--
Danny J. Lesandrini
dl*********@hotmail.com
http://amazecreations.com/datafast
Option Compare Database
Option Explicit

Function WriteASP(ByVal sTable As String) As Boolean
On Error GoTo Err_Handler

Dim sASP As String
Dim sSQL As String
Dim sDBName As String
Dim q As String
Dim t As String
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim prp As DAO.Property

Dim sField As String
Dim sCaption As String
Dim sCtl As String
Dim sCtlRef As String
Dim sHidden As String
Dim intSize As Integer
Dim sVariables As String
Dim sVar As String
Dim sInsertVal As String
Dim sForm As String
Dim sIDCheck As String

Dim sAssignments As String
Dim sPost As String
Dim sStart As String
Dim sMid As String
Dim sClose As String
Dim sInput As String
Dim sFile As String
Dim sPath As String
Dim sINSERT As String
Dim sINSArgList As String
Dim sINSValues As String
Dim sUPDValues As String
Dim sUPDATE As String
Dim sUPDWhere As String
Dim sDelim As String
Dim sFetch As String
Dim sRecords As String
Dim sFetchSQL As String
Dim fPK As Boolean
Dim sSampleLink As String

' Execute example
' ?WriteASP("tblProgram")

Set dbs = CurrentDb
Set tdf = dbs.TableDefs(sTable)
sDBName = Right(dbs.Name, Len(dbs.Name) - LastInStr(dbs.Name, "\"))

q = """"
t = " "

sPath = Left(CurrentDb.Name, LastInStr(CurrentDb.Name, "\"))
'sFile = sPath & sTable & ".txt"
sFile = sPath & sTable & ".asp"

If Len(Dir(sFile)) > 0 Then Kill sFile
sVariables = "<%" & vbCrLf & t & "Dim varID, "
sStart = t & t & "<tr valign=" & q & "top" & q & "><td width=" & q & 150 & q & "> "
sMid = t & t & "</td><td width=" & q & 255 & q & "> " & vbCrLf
sClose = t & t & "</td></tr>" & vbCrLf
sInput = t & t & t & "<INPUT style=" & q & "FONT-SIZE: xx-small; WIDTH: "

sINSArgList = t & t & "sSQL = " & q & "INSERT INTO " & sTable & " ("
sINSValues = t & t & "sSQL = sSQL & " & q & " VALUES("
sUPDValues = t & t & "sSQL = " & q & "UPDATE " & sTable & " SET " & q & vbCrLf

sIDCheck = ""

sForm = "<FORM method=post action=" & sTable & ".asp?ID=<%=varID%> id=frm" & sTable & " name=frm" & sTable & ">" &
vbCrLf & vbCrLf
sForm = sForm & "<INPUT type=submit value=Save id=btnSave name=btnSave>" & vbCrLf & vbCrLf
sASP = t & "<table border=" & q & "1" & q & " width=" & q & "100%" & q & " id=" & q & sTable & q & ">" & vbCrLf

On Error Resume Next

For Each fld In tdf.Fields
sField = fld.Name

If sField = "OfficialCKSchool" Then
Beep
End If

sCaption = fld.Properties("Caption")
sVar = "var" & sField
sCtl = "txt" & sField
sDelim = GetFieldDeliminator(fld.Type)
intSize = fld.Size
sCtlRef = "Request.Form(" & q & sCtl & q & ")"

If sCaption = "" Then sCaption = sField
If intSize < 25 Then intSize = 25
If (fld.Attributes And dbAutoIncrField) Then fPK = True Else fPK = False
If fPK Then
sFetchSQL = t & "sSQL = " & q & "SELECT * FROM " & sTable & " WHERE " & sField & " = " & q & " & varID"
sSampleLink = t & "<a href=" & sTable & ".asp?ID=" & DMax(fld.Name, sTable) & ">Go To Sample Record</a>"
End If

If sCaption <> "AutoNumber" Then

If sDelim = "'" Then
' Replace single quotes with two single quotes so SQL statement doesn't break
' Truncate user response to size limit of field, to avoid INSERT/UPDATE error.
sInsertVal = "Left(Replace(" & sVar & "," & q & "'" & q & "," & q & "''" & q & ")," & fld.Size & ")"
Else
sInsertVal = sVar
End If

If Not fPK Then
sINSArgList = sINSArgList & sField & ","
sINSValues = sINSValues & sDelim & q & " & " & sInsertVal & " & " & q & sDelim & ","
sUPDValues = sUPDValues & t & t & "sSQL = sSQL & " & q & sField & "=" & sDelim & q & " & " & sInsertVal
& " & " & q & sDelim & "," & q & vbCrLf
End If
sHidden = ""
sRecords = sRecords & t & t & t & sVar & "=rsDataTable(" & q & sField & q & ")" & vbCrLf
Else
sCaption = vbCrLf & t & t & t & "<INPUT type=" & q & "submit" & q & " value=" & q & "Save" & q & " id=" & q
& "btnSave" & q & " name=" & q & "btnSave" & q & ">" & vbCrLf
sUPDWhere = t & t & "sSQL = sSQL & " & q & " WHERE " & sField & " = " & q & " & varID" & vbCrLf
sHidden = " Type=" & q & "hidden" & q
End If

sVariables = sVariables & sVar & ","
sAssignments = sAssignments & t & sVar & " = " & sCtlRef & vbCrLf

sASP = sASP & sStart & sCaption & vbCrLf
sASP = sASP & sMid & sInput & intSize & "px" & q
sASP = sASP & " id=" & q & "txt" & sField & q & _
" name=" & q & "txt" & sField & q & _
" value=" & q & "<%=Trim(" & sVar & ")%>" & q
sASP = sASP & sHidden & ">" & vbCrLf & sClose & vbCrLf

' Reset variables
sCaption = ""
Next

sFetch = vbCrLf
sFetch = sFetch & t & "Function RetrieveRecord(varID)" & vbCrLf
sFetch = sFetch & t & t & "Dim sSQL, sPath, sConn, cnnDBS, rsDataTable" & vbCrLf & vbCrLf
sFetch = sFetch & t & t & "Set cnnDBS = server.CreateObject(" & q & "adodb.connection" & q & ")" & vbCrLf

sFetch = sFetch & t & t & "sPath = LCase(Server.MapPath(" & q & sTable & ".asp" & q & "))" & vbCrLf
sFetch = sFetch & t & t & "sPath = Replace(sPath, " & q & LCase(sTable) & ".asp" & q & ", " & q & sDBName & q & ")"
& vbCrLf
sFetch = sFetch & t & t & "sConn = " & q & "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data
Source=" & q & " & sPath" & vbCrLf
sFetch = sFetch & t & t & "cnnDBS.Open (sConn)" & vbCrLf & vbCrLf

sFetch = sFetch & t & t & "Set rsDataTable = server.CreateObject(" & q & "adodb.recordset" & q & ")" & vbCrLf
sFetch = sFetch & t & t & sFetchSQL & vbCrLf
sFetch = sFetch & t & t & "Set rsDataTable = cnnDBS.Execute(sSQL)" & vbCrLf & vbCrLf
sFetch = sFetch & t & t & "If Not rsDataTable.BOF And Not rsDataTable.EOF Then" & vbCrLf
sFetch = sFetch & sRecords & vbCrLf
sFetch = sFetch & t & t & "End If" & vbCrLf
sFetch = sFetch & t & t & "Set rsDataTable = Nothing" & vbCrLf
sFetch = sFetch & t & t & "Set cnnDBS = Nothing" & vbCrLf & vbCrLf
sFetch = sFetch & t & "End Function" & vbCrLf
sPost = sPost & vbCrLf
sPost = sPost & t & "varID = Request.QueryString(" & q & "ID" & q & ")" & vbCrLf
sPost = sPost & t & "If varID = " & q & q & " Then varID = " & q & "0" & q & vbCrLf & vbCrLf
sPost = sPost & t & "If Request(" & q & "btnSave" & q & ") <> " & q & q & " Then" & vbCrLf
sPost = sPost & t & t & "If varID = " & q & "0" & q & " Then" & vbCrLf
sPost = sPost & t & t & t & "varID = InsertNewRecord()" & vbCrLf
sPost = sPost & t & t & "Else" & vbCrLf
sPost = sPost & t & t & t & "Call UpdateRecord(varID)" & vbCrLf
sPost = sPost & t & t & "End If" & vbCrLf
sPost = sPost & t & "Else" & vbCrLf
sPost = sPost & t & t & "Call RetrieveRecord(varID)" & vbCrLf
sPost = sPost & t & "End If" & vbCrLf & vbCrLf
sINSArgList = Left(sINSArgList, Len(sINSArgList) - 1) & ") " & q & vbCrLf
sINSValues = Left(sINSValues, Len(sINSValues) - 1) & ") " & q & vbCrLf

sINSERT = t & "Function InsertNewRecord()" & vbCrLf
sINSERT = sINSERT & t & t & "Dim sSQL, varID, sPath, sConn, cnnDBS, rsDataTable" & vbCrLf & vbCrLf
sINSERT = sINSERT & t & t & "Set cnnDBS = server.CreateObject(" & q & "adodb.connection" & q & ")" & vbCrLf

sINSERT = sINSERT & t & t & "sPath = LCase(Server.MapPath(" & q & sTable & ".asp" & q & "))" & vbCrLf
sINSERT = sINSERT & t & t & "sPath = Replace(sPath, " & q & LCase(sTable) & ".asp" & q & ", " & q & sDBName & q &
")" & vbCrLf
sINSERT = sINSERT & t & t & "sConn = " & q & "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data
Source=" & q & " & sPath" & vbCrLf
sINSERT = sINSERT & t & t & "cnnDBS.Open (sConn)" & vbCrLf & vbCrLf

sINSERT = sINSERT & sINSArgList & sINSValues & vbCrLf
sINSERT = sINSERT & t & t & "sSQL = Replace(sSQL," & q & "'Null'" & q & "," & q & "Null" & q & ")" & vbCrLf
sINSERT = sINSERT & t & t & "sSQL = Replace(sSQL," & q & ",," & q & "," & q & ",Null," & q & ")" & vbCrLf
sINSERT = sINSERT & t & t & "sSQL = Replace(sSQL," & q & ",," & q & "," & q & ",Null," & q & ")" & vbCrLf
sINSERT = sINSERT & t & t & "cnnDBS.Execute (sSQL)" & vbCrLf & vbCrLf
sINSERT = sINSERT & t & t & "sSQL = " & q & "SELECT Max([SchlID]) As ID FROM " & sTable & q & vbCrLf
sINSERT = sINSERT & t & t & "Set rsDataTable = server.CreateObject(" & q & "adodb.recordset" & q & ") " & vbCrLf
sINSERT = sINSERT & t & t & "Set rsDataTable = cnnDBS.Execute(sSQL)" & vbCrLf
sINSERT = sINSERT & t & t & "InsertNewRecord = rsDataTable(" & q & "ID" & q & ")" & vbCrLf & vbCrLf
sINSERT = sINSERT & t & t & "If rsDataTable.BOF And rsDataTable.EOF Then" & vbCrLf
sINSERT = sINSERT & t & t & t & "InsertNewRecord = 0" & vbCrLf
sINSERT = sINSERT & t & t & "Else" & vbCrLf
sINSERT = sINSERT & t & t & t & "InsertNewRecord = rsDataTable(" & q & "ID" & q & ")" & vbCrLf
sINSERT = sINSERT & t & t & "End If" & vbCrLf & vbCrLf
sINSERT = sINSERT & t & t & "Set rsDataTable = Nothing" & vbCrLf
sINSERT = sINSERT & t & t & "Set cnnDBS = Nothing" & vbCrLf & vbCrLf
sINSERT = sINSERT & t & "End Function" & vbCrLf
sUPDValues = Left(sUPDValues, Len(sUPDValues) - 4) & q & vbCrLf
sUPDValues = sUPDValues & sUPDWhere & vbCrLf

sUPDATE = t & "Function UpdateRecord(varID)" & vbCrLf
sUPDATE = sUPDATE & t & t & "Dim sSQL, sPath, sConn, cnnDBS" & vbCrLf & vbCrLf
sUPDATE = sUPDATE & t & t & "Set cnnDBS = server.CreateObject(" & q & "adodb.connection" & q & ")" & vbCrLf

sUPDATE = sUPDATE & t & t & "sPath = LCase(Server.MapPath(" & q & sTable & ".asp" & q & "))" & vbCrLf
sUPDATE = sUPDATE & t & t & "sPath = Replace(sPath, " & q & LCase(sTable) & ".asp" & q & ", " & q & sDBName & q &
")" & vbCrLf
sUPDATE = sUPDATE & t & t & "sConn = " & q & "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data
Source=" & q & " & sPath" & vbCrLf
sUPDATE = sUPDATE & t & t & "cnnDBS.Open (sConn)" & vbCrLf & vbCrLf

sUPDATE = sUPDATE & sUPDValues & vbCrLf
'sUPDATE = sUPDATE & t & t & "sSQL = Replace(sSQL," & q & "''" & q & "," & q & "Null" & q & ")" & vbCrLf
sUPDATE = sUPDATE & t & t & "sSQL = Replace(sSQL," & q & "=''" & q & "," & q & "=Null" & q & ")" & vbCrLf
sUPDATE = sUPDATE & t & t & "sSQL = Replace(sSQL," & q & "=," & q & "," & q & "=Null," & q & ")" & vbCrLf
sUPDATE = sUPDATE & t & t & "cnnDBS.Execute (sSQL)" & vbCrLf & vbCrLf
sUPDATE = sUPDATE & t & t & "Set cnnDBS = Nothing" & vbCrLf & vbCrLf
sUPDATE = sUPDATE & t & "End Function" & vbCrLf

sVariables = Left(sVariables, Len(sVariables) - 1) & vbCrLf & vbCrLf
sAssignments = sAssignments & vbCrLf & vbCrLf
sPost = sPost & vbCrLf & vbCrLf & "%>" & vbCrLf & vbCrLf
sASP = sVariables & sAssignments & sPost & sForm & sASP & t & "</table>" & vbCrLf & vbCrLf & "<%" & vbCrLf & vbCrLf
sASP = sASP & sINSERT & vbCrLf & sUPDATE & vbCrLf & sFetch & vbCrLf & "%>" & vbCrLf & vbCrLf & "</FORM>" & vbCrLf

Set tdf = Nothing
Set dbs = Nothing

WriteASP = (Err.Number = 0)

sASP = "<br>" & sSampleLink & "<br><br>" & sASP

WriteToFile sFile, sASP
Shell "Notepad.exe " & sFile, vbNormalFocus

Exit Function

Err_Handler:
MsgBox Err.Description
Resume Next

End Function

Function GetFieldDeliminator(ByVal intFieldType As Integer) As String
On Error Resume Next

Select Case intFieldType
Case dbBigInt: GetFieldDeliminator = ""
Case dbBinary: GetFieldDeliminator = ""
Case dbBoolean: GetFieldDeliminator = ""
Case dbByte: GetFieldDeliminator = ""
Case dbChar: GetFieldDeliminator = "'"
Case dbCurrency: GetFieldDeliminator = ""
Case dbDate: GetFieldDeliminator = "#"
Case dbDecimal: GetFieldDeliminator = ""
Case dbDouble: GetFieldDeliminator = ""
Case dbFloat: GetFieldDeliminator = ""
Case dbGUID: GetFieldDeliminator = "'"
Case dbInteger: GetFieldDeliminator = ""
Case dbLong: GetFieldDeliminator = ""
Case dbLongBinary: GetFieldDeliminator = ""
Case dbMemo: GetFieldDeliminator = "'"
Case dbNumeric: GetFieldDeliminator = ""
Case dbSingle: GetFieldDeliminator = ""
Case dbText: GetFieldDeliminator = "'"
Case dbTime: GetFieldDeliminator = "#"
Case dbTimeStamp: GetFieldDeliminator = ""
Case dbVarBinary: GetFieldDeliminator = ""
End Select

End Function

Function WriteToFile(ByVal sFile As String, ByVal sText As String)
On Error Resume Next

Dim intFileNum As Integer
intFileNum = FreeFile
Open sFile For Append Shared As intFileNum
Print #intFileNum, sText

Close #intFileNum

End Function
Function LastInStr(sSearched As String, sSought As String) As Integer
On Error Resume Next

'//////////////////////////////////////////////////////////////////////////
'
' This function finds the last instance of a character within
' a String of characters and returns an integer representing
' the final position of the desired character.
'
' Typically, this function us used to find the final "\" in
' a file path String
'
'//////////////////////////////////////////////////////////////////////////

Dim intCurrVal As Integer
Dim intLastPosition As Integer

intCurrVal = InStr(sSearched, sSought)
Do Until intCurrVal = 0
intLastPosition = intCurrVal
intCurrVal = InStr(intLastPosition + 1, sSearched, sSought)
Loop
LastInStr = intLastPosition

End Function

[Jerry Boone]

If your users are remote and have broadband, and you have a way let them hit
the sql server (vpn or open a sql server port) then using Access Projects
will do these things for you...

Keep your application in Access, but not without some drastic conversion to
sql server
Have all the same gui features the users like to use... filter, sort, etc...
Let your clients work in Access from anywhere with decent speed by
leveraging the native oldeb connectivity with Sql Server
Deploy faster than ASP or ASP.Net by an unimaginable factor
Deploy ADE files - they will never grow with the data since they only hold
the connection info, forms, and reports. This makes them small and easy to
send to users in which they use the file locally.

You can get many of these advantages by redeploying an mdb with converted
linked sql tables, but you will incur more overhead with odbc and jet than
with just oledb.

Terminal server is good, but then you have to buy 25 licenses, beef up the
server pretty hard, and then train your users not to do things they
shouldn't. I found it was easy for some users to forget they were on a
terminal.

See "A good reason to use ADP's - 1/5/03 2:43pm" for more of my ideas on
this type of deployment. We do this for a living.

--
Jerry Boone
Analytical Technologies, Inc.
http://www.antech.biz
Secure Hosting and Development Solutions for ASP, ASP.NET, SQL Server, and
Access

"RCS" <robsol{at}tower{DOT}ne{DOT}au> wrote in message
news:3f**********************@freenews.iinet.net.a u...

Okay, a rather 'interesting' situation has arisen at a place I work: I need to convert a database from Access to something that can be used over the
web.

I am currently maintaining and developing a mid-sized Access database (60
tables, 25 simultaneous users).
Although I've been tossing up if I should try to convert the back end to SQL server (We have a license), the performance has been good enough that it
always seemed a waste of time, so it's stayed as an MDB, split into front
and back end.

Due to recent changes in situation, certain individuals have decided the
entire database needs to be put on the web.

Unfortunately, I got stuck with the job... and it was all decided before
anyone bothered to see what it actually involved.
Basically, I was told to take an Access database and make it all webby...
And now I'm pretty much stuck for where to look next.

I know the back end it going to have to be SQL. Converting the tables
shouldn't be too much of a problem.

But the front end is where I run in to trouble- obviously a web browser will be used, but I'm going to need something to generate the HTML pages. Last
time I looked in to it, you had to learn something like VBscript or CGI and do it all manually.
I'm hoping there are better tools around now. (I've really gotten to like
the form editor on Access! )

While I doubt there will be anything that can convert any of the work that's been done (Forms and reports), I was wondering if anyone had any
reccomendations on how to get started on this kind of project- because in
all honesty I'd never even considered it before now....

[RCS]

Tom van Stiphout <to*****@no.spam.cox.net> wrote in message
news:it********************************@4ax.com...

On Wed, 7 Jan 2004 10:49:34 +0800, "RCS"
<robsol{at}tower{DOT}ne{DOT}au> wrote:

For you to rewrite this app as an ASP app in VBScript would be
impossible - at least as your first web project. Unless you have A LOT
of time and can learn as you go, at least a couple of months.
Pretty much what I thought. I was certain it wouldn't be a 'finished by the
weekend' task as some people were expecting. ;)
If this is an intranet solution (i.e. the only access is from inside
the company), you may want to look into the capabilities all recent
Office apps have to be delivered in a browser. This requires IE,
Access on every desktop, and a fast LAN connection.
Unfortunately not. Remote access is also out as well.

Sometimes a hybrid solution is possible, whereby the office continues
to use the Access app, perhaps with SQL back-end, and some web users
(perhaps some clients?) use a limited set of web forms (e.g. view
status of my orders, view account balance, submit new order, ...)

Now that's a fantastic idea! I could probably get away with something like
that... at least for a year or so. I'll look in to it- thanks!