473,728 Members | 1,660 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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....
Nov 12 '05 #1
7 8689
On Wed, 7 Jan 2004 10:49:34 +0800, "RCS" <robsol{at}towe r{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.

Unfortunatel y, 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
reccomendation s 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.
Nov 12 '05 #2
On Wed, 7 Jan 2004 10:49:34 +0800, "RCS"
<robsol{at}towe r{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.

Unfortunatel y, 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
reccomendation s on how to get started on this kind of project- because in
all honesty I'd never even considered it before now....


Nov 12 '05 #3
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}towe r{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....

Nov 12 '05 #4
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*********@hot mail.com
http://amazecreations.com/datafast
"RCS" <robsol{at}towe r{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....

Nov 12 '05 #5
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*********@hot mail.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("tblP rogram")

Set dbs = CurrentDb
Set tdf = dbs.TableDefs(s Table)
sDBName = Right(dbs.Name, Len(dbs.Name) - LastInStr(dbs.N ame, "\"))

q = """"
t = " "

sPath = Left(CurrentDb. Name, LastInStr(Curre ntDb.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=<%=var ID%> 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 = "OfficialCKScho ol" Then
Beep
End If

sCaption = fld.Properties( "Caption")
sVar = "var" & sField
sCtl = "txt" & sField
sDelim = GetFieldDelimin ator(fld.Type)
intSize = fld.Size
sCtlRef = "Request.Fo rm(" & 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(Repla ce(" & 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 & "=rsDataTab le(" & 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.CreateOb ject(" & q & "adodb.connecti on" & q & ")" & vbCrLf

sFetch = sFetch & t & t & "sPath = LCase(Server.Ma pPath(" & 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=Micro soft.Jet.OLEDB. 4.0;Persist Security Info=False;Data
Source=" & q & " & sPath" & vbCrLf
sFetch = sFetch & t & t & "cnnDBS.Ope n (sConn)" & vbCrLf & vbCrLf

sFetch = sFetch & t & t & "Set rsDataTable = server.CreateOb ject(" & q & "adodb.recordse t" & 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.QuerySt ring(" & 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(va rID)" & 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(sINSArgLis t, 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.CreateOb ject(" & q & "adodb.connecti on" & q & ")" & vbCrLf

sINSERT = sINSERT & t & t & "sPath = LCase(Server.Ma pPath(" & 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=Micro soft.Jet.OLEDB. 4.0;Persist Security Info=False;Data
Source=" & q & " & sPath" & vbCrLf
sINSERT = sINSERT & t & t & "cnnDBS.Ope n (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.Exe cute (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.CreateOb ject(" & q & "adodb.recordse t" & q & ") " & vbCrLf
sINSERT = sINSERT & t & t & "Set rsDataTable = cnnDBS.Execute( sSQL)" & vbCrLf
sINSERT = sINSERT & t & t & "InsertNewRecor d = rsDataTable(" & q & "ID" & q & ")" & vbCrLf & vbCrLf
sINSERT = sINSERT & t & t & "If rsDataTable.BOF And rsDataTable.EOF Then" & vbCrLf
sINSERT = sINSERT & t & t & t & "InsertNewRecor d = 0" & vbCrLf
sINSERT = sINSERT & t & t & "Else" & vbCrLf
sINSERT = sINSERT & t & t & t & "InsertNewRecor d = 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(va rID)" & vbCrLf
sUPDATE = sUPDATE & t & t & "Dim sSQL, sPath, sConn, cnnDBS" & vbCrLf & vbCrLf
sUPDATE = sUPDATE & t & t & "Set cnnDBS = server.CreateOb ject(" & q & "adodb.connecti on" & q & ")" & vbCrLf

sUPDATE = sUPDATE & t & t & "sPath = LCase(Server.Ma pPath(" & 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=Micro soft.Jet.OLEDB. 4.0;Persist Security Info=False;Data
Source=" & q & " & sPath" & vbCrLf
sUPDATE = sUPDATE & t & t & "cnnDBS.Ope n (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.Exe cute (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.ex e " & sFile, vbNormalFocus

Exit Function

Err_Handler:
MsgBox Err.Description
Resume Next

End Function

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

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

End Function

Function WriteToFile(ByV al 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(sSear ched 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(intLastPo sition + 1, sSearched, sSought)
Loop
LastInStr = intLastPosition

End Function

Nov 12 '05 #6
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}towe r{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....

Nov 12 '05 #7
RCS

Tom van Stiphout <to*****@no.spa m.cox.net> wrote in message
news:it******** *************** *********@4ax.c om...
On Wed, 7 Jan 2004 10:49:34 +0800, "RCS"
<robsol{at}towe r{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!
Nov 12 '05 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

17
2899
by: chicha | last post by:
Hey people, I have to convert MS Access 2000 database into mysql database, the whole thing being part of this project I'm doing for one of my faculty classes. My professor somehow presumed I knew db's and gave me long list of things to do with that particular database, first thing being that particular conversion. Truth is that I don't know a first thing about db's, let alone using mysql... I downloaded mysql form www.mysql.com and...
5
2303
by: Terry Bell | last post by:
I'm upsizing an Access database. Got the data converted, working on the front end, converting queries to views, but having trouble converting queries that use logical expressions like the following: SELECT OrderId, Sum((BackOrderQtyAvailable>0)*-1) AS ReadyBackOrderItems FROM OrderDetails INNER JOIN Items ON (OrderDetails.ClientId = Items.ClientId) AND (OrderDetails.ItemId = Items.ItemId) WHERE (NOT (SitesCustomerTypeId = 2
1
1396
by: Tom LeBold | last post by:
Do White papers or other documents exist that explain the steps to converting an Access database and/or tables to SQL 2000.
3
4082
by: Leinad Kong | last post by:
I'm using Access 2002, as front-end and back-end as well: 1) I faced database corrupted problems, when more than 1 user edit concurrently. I'm using All-records Locking, and open-exclusively as default. But, still face the same problem: Database corrupted! (But, it allows to be repaired) 2) I found that the relationship linkage between those related tables(which corrupted) was disconnected/lost after problem (1)
14
2445
by: D | last post by:
Hey guys- not sure where this post fits in, so I cc'd a few other groups as well- hope you dont mind... I have someone creating a database for me in Access 2000 (or is it called XP?). When it's all done, I'd like it to be online via the web. Ultimately I'd like just an online database accessed via ASP, PHP, CGI, etc- something (doesn't matter) but where various employees can access the thing from anywhere and do their work. It's been...
8
3147
by: John Baker | last post by:
Hi: I am URGENTLY in need of some book or web site OR tool that will help me integrate a relatively simple access application into a web page or pages. This is a time recording system (by project), and I would be more than wiling to pull the updated database down from the Host using FTP on a monthly basis. Its just that I need to understand how to set it up on the web site itself. The Host supports SQL. Any direction you can give would...
9
3832
by: Wayne Smith | last post by:
I've come up against a major headache that I can't seem to find a solution for but I'm sure there must be a workaround and I would really be grateful of any help. I'm currently building a web site for a small club I belong to and one of the features I would like to include is the ability to allow users to upload image files. unfortunately the servers web root www folder only allows READ and EXECUTE permissions, which makes it...
5
6453
by: Stewart | last post by:
Hi there, I have an Access database, which contains the details of company staff and services. The plan is to extract data from this database onto our forthcoming Intranet (no inserting, updating or deleting at this point). The Intranet itself has been created in ASP.NET, using Microsoft Visual Web Developer 2005. My concern is that we will encounter a slow response when pulling data from this Access database across the network...
21
4108
by: nihad.nasim | last post by:
Hi there, I have a database in Access that I need on the web. The web page should connect to the database and write records for certain tables and view records for others. I want to know a reliable way of connecting Access to a server. I am willing to switch to any version of Access which might solve the problem. Which server would you recommend and what are the advanatages and disadvantatges of the server you propose? Please also inlcude...
4
2194
by: dewald4305 | last post by:
I just converted an Access database from Access 2000 to Access 2003. My Combo boxes are now not working. They do not display anything in the drop-downs. What do I need to change to make this work? Any help would be appreciated!
0
8757
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9272
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
9129
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
8124
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6712
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6013
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4790
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3235
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2651
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.