By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,851 Members | 1,047 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,851 IT Pros & Developers. It's quick & easy.

Simple htm vbscript snippet for appending database record

P: n/a
Does anyone have a simple html vbscript or other type of snippet they
can share that appends a record to a access database via ADO or DAO?
I would like to allow users that don't have Microsoft Access a way of
adding records to a access database from a simple web page. I don't
want to have to setup ODBC or anything like that I just want to put
the web page on the network for anyone to access. Most of the users
would use the local access front end but those who don't have Access
would just open up the .htm file and post the information from the web
form. If anyone has the time to cut & paste an example that would be
great.
Nov 13 '05 #1
Share this Question
Share on Google+
9 Replies


P: n/a
On 23 Nov 2004 11:33:29 -0800, so**********@netzero.net (Pete) wrote:
Does anyone have a simple html vbscript or other type of snippet they
can share that appends a record to a access database via ADO or DAO?
I would like to allow users that don't have Microsoft Access a way of
adding records to a access database from a simple web page. I don't
want to have to setup ODBC or anything like that I just want to put
the web page on the network for anyone to access. Most of the users
would use the local access front end but those who don't have Access
would just open up the .htm file and post the information from the web
form. If anyone has the time to cut & paste an example that would be
great.


Hi
Do you mean local / networked database?
If script is not disabled you can run a simple .vbs file such as

Set conn = WScript.CreateObject("ADODB.Connection")
MdbFilePath = "C:\db1.mdb"
conn.Open "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" &
MdbFilePath & ";"

SQL = "INSERT INTO Table1 ( colour, path ) SELECT [colour]+1 AS Expr1,
[path] & '1' AS Expr2 FROM Table1;"

conn.Execute(SQL)
conn.Close

Or did you mean a server database, using ASP? Code would be similar,
something like

<%
Set conn = server.CreateObject("ADODB.Connection")

MdbFilePath = Server.MapPath("db1.mdb")
conn.Open "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" &
MdbFilePath & ";"

SQL = "INSERT INTO Table1 ( colour, path ) SELECT [colour]+1
AS Expr1, [path] & '1' AS Expr2 FROM Table1;"

conn.Execute(SQL)
conn.Close
%>

David
Nov 13 '05 #2

P: n/a
Yes it would be a network backend .mdb database. That's exactly the
syntax I'm looking for but I need to embed this in a simple .html file
with a textbox and button so that users can insert the record to the
mdb via the vbscript. Would you happen to have the syntax to activate
this from a web page? Any method will do (vbscript asp ...) Thanks
Nov 13 '05 #3

P: n/a
On 24 Nov 2004 10:22:19 -0800, so**********@netzero.net (Pete) wrote:
Yes it would be a network backend .mdb database. That's exactly the
syntax I'm looking for but I need to embed this in a simple .html file
with a textbox and button so that users can insert the record to the
mdb via the vbscript. Would you happen to have the syntax to activate
this from a web page? Any method will do (vbscript asp ...) Thanks


Hi
See the example at
http://www.4guysfromrolla.com/webtec...tml#postadlink
which gives generic code for database insert via HTML form and ASP.
David

Nov 13 '05 #4

P: n/a

"Pete" <so**********@netzero.net> wrote in message
news:8f**************************@posting.google.c om...
Yes it would be a network backend .mdb database. That's exactly the
syntax I'm looking for but I need to embed this in a simple .html file
with a textbox and button so that users can insert the record to the
mdb via the vbscript. Would you happen to have the syntax to activate
this from a web page? Any method will do (vbscript asp ...) Thanks

If you need only a single textbox, to get data from the user, then using a
stand-alone vb-script would be the simplest (without using a web page) but
using a web-page would give you more control and if you used something like
asp, then no-one could see/amend the script. However, there are two
hurdles: Firstly, you need to have a machine running IIS, and you should try
to publish an initial test page first. Once you have that working you can
create the script to add the record, but you need to let us know something
about the table structure for the record to be added. For example, database
has a single table tblContacts with two fields, ConID is an autonumber field
which serves as the primary key and ConName is the contact's name which is
input by the user. With that sort of info, someone could write the script.
Nov 13 '05 #5

P: n/a
"Eric Schittlipz" <er**@schittlipz.com> wrote in message news:<co**********@hercules.btinternet.com>...
"Pete" <so**********@netzero.net> wrote in message
news:8f**************************@posting.google.c om...
Yes it would be a network backend .mdb database. That's exactly the
syntax I'm looking for but I need to embed this in a simple .html file
with a textbox and button so that users can insert the record to the
mdb via the vbscript. Would you happen to have the syntax to activate
this from a web page? Any method will do (vbscript asp ...) Thanks

If you need only a single textbox, to get data from the user, then using a
stand-alone vb-script would be the simplest (without using a web page) but
using a web-page would give you more control and if you used something like
asp, then no-one could see/amend the script. However, there are two
hurdles: Firstly, you need to have a machine running IIS, and you should try
to publish an initial test page first. Once you have that working you can
create the script to add the record, but you need to let us know something
about the table structure for the record to be added. For example, database
has a single table tblContacts with two fields, ConID is an autonumber field
which serves as the primary key and ConName is the contact's name which is
input by the user. With that sort of info, someone could write the script.

You could try a simple html application. You would probably want to
add some more robust data validation. It doesn't require a server.
Only IE 5 and above and vbScript.

http://msdn.microsoft.com/workshop/a...taoverview.asp

The example below is very basic but should give you an idea of what it
does.

<html>
<head>
<title>
Data Add Form
</title>
<script language="vbscript">
sub cmdAdd_onClick
dim dbEng
dim wrksp
dim db

if VerifyData()=true then

set dbEng = createobject("dao.dbengine.36")
dbEng.systemdb = "pathtoworkgroupfile"
dbEng.defaultuser = "username"
dbEng.defaultpassword = "password"

set wrksp = dbEng.workspaces(0)

set db = wrksp.opendatabase("PathToDataBase",false,false)

db.execute "INSERT INTO tblData(DataField) " _
& "VALUES('" & txtData.value & "');",128

msgbox "Data Added"

txtData.value = ""

db.close
set db = nothing
wrksp.close
set wrksp = nothing
set dbEng = nothing

else

msgbox "Hey enter Data"
txtData.value=""

end if

end sub

function VerifyData()

if len(trim(txtData.value))=7 then

if isnumeric(txtData.value) then

VerifyData=true

else

VerifyData=false

end if

else

VerifyData=false

end if

end function

</script>
</head>
<body>
<fieldset>
<legend>Enter data to add</legend>
<center>
<input type="text" size="7" maxlength="7" name="txtData"></br>
</p>
<input type="button" name="cmdAdd" value="Add">
</center>
</fieldset>
</br>
</body>
</html>
Nov 13 '05 #6

P: n/a
"Dan Morgan" <us****@yahoo.com> wrote in message
news:fe**************************@posting.google.c om...
"Eric Schittlipz" <er**@schittlipz.com> wrote in message
news:<co**********@hercules.btinternet.com>...
"Pete" <so**********@netzero.net> wrote in message
news:8f**************************@posting.google.c om...
> Yes it would be a network backend .mdb database. That's exactly the
> syntax I'm looking for but I need to embed this in a simple .html file
> with a textbox and button so that users can insert the record to the
> mdb via the vbscript. Would you happen to have the syntax to activate
> this from a web page? Any method will do (vbscript asp ...) Thanks

If you need only a single textbox, to get data from the user, then using
a
stand-alone vb-script would be the simplest (without using a web page)
but
using a web-page would give you more control and if you used something
like
asp, then no-one could see/amend the script. However, there are two
hurdles: Firstly, you need to have a machine running IIS, and you should
try
to publish an initial test page first. Once you have that working you
can
create the script to add the record, but you need to let us know
something
about the table structure for the record to be added. For example,
database
has a single table tblContacts with two fields, ConID is an autonumber
field
which serves as the primary key and ConName is the contact's name which
is
input by the user. With that sort of info, someone could write the
script.

You could try a simple html application. You would probably want to
add some more robust data validation. It doesn't require a server.
Only IE 5 and above and vbScript.

http://msdn.microsoft.com/workshop/a...taoverview.asp

The example below is very basic but should give you an idea of what it
does.

<html>
<head>
<title>
Data Add Form
</title>
<script language="vbscript">
sub cmdAdd_onClick
dim dbEng
dim wrksp
dim db

if VerifyData()=true then

set dbEng = createobject("dao.dbengine.36")
dbEng.systemdb = "pathtoworkgroupfile"
dbEng.defaultuser = "username"
dbEng.defaultpassword = "password"

set wrksp = dbEng.workspaces(0)

set db = wrksp.opendatabase("PathToDataBase",false,false)

db.execute "INSERT INTO tblData(DataField) " _
& "VALUES('" & txtData.value & "');",128

msgbox "Data Added"

txtData.value = ""

db.close
set db = nothing
wrksp.close
set wrksp = nothing
set dbEng = nothing

else

msgbox "Hey enter Data"
txtData.value=""

end if

end sub

function VerifyData()

if len(trim(txtData.value))=7 then

if isnumeric(txtData.value) then

VerifyData=true

else

VerifyData=false

end if

else

VerifyData=false

end if

end function

</script>
</head>
<body>
<fieldset>
<legend>Enter data to add</legend>
<center>
<input type="text" size="7" maxlength="7" name="txtData"></br>
</p>
<input type="button" name="cmdAdd" value="Add">
</center>
</fieldset>
</br>
</body>
</html>


Agreed this has some advantages over a simple vbs file, especially when
there needs to be some form of user interface. I did write a quick one for
someone who needed to do some data manipulation with word tables but didn't
have Access. However, I haven't ever sold an hta application. The idea
never seemed to take off and you don't really see many around.

Nov 13 '05 #7

P: n/a
You answered my question. A statement that I know is unfortunately
true: new records cannot be added from a web page without having IIS
or some other type of web server on the client or server side. Simply
put you can not post any web page on your company's network and expect
it to interact with your database without having administrative access
and IIS. The whole reason behind this is to develop a method for
inserting records without having Access installed on the client's Pc.
I suppose one crude solution is to build a connection from excel since
most client pc have excel installed. The other solution I thought of
is to develop a simple 32bit vb .exe file with a standard input form
and copy that app if they didn't have access installed. If anyone
else has got a easier solution please let me know.
Nov 13 '05 #8

P: n/a

"Pete" <so**********@netzero.net> wrote in message
news:8f**************************@posting.google.c om...
You answered my question. A statement that I know is unfortunately
true: new records cannot be added from a web page without having IIS
or some other type of web server on the client or server side. Simply
put you can not post any web page on your company's network and expect
it to interact with your database without having administrative access
and IIS. The whole reason behind this is to develop a method for
inserting records without having Access installed on the client's Pc.
I suppose one crude solution is to build a connection from excel since
most client pc have excel installed. The other solution I thought of
is to develop a simple 32bit vb .exe file with a standard input form
and copy that app if they didn't have access installed. If anyone
else has got a easier solution please let me know.

IIS would be needed for ASP, but it's not the only possibility if you need
web pages. Did you see Dan's suggestion? Just amend the code slightly with
your own database details, and save this plain text file with a .hta
extension. Otherwise creating an exe file with something like Visual Basic
would be good. Another solution would be to create a runtime installation
using the Developer's version of Access, but that would probably be too much
for a simple application.
Nov 13 '05 #9

P: n/a
Yep. I thought there was something like this out there. After
searching and searching I was ready to give up until I saw the post
from Dan about the HTA solution. Wow this is exactly what I was
looking for. A simple solution that I actually got to work. Now I'll
have to test it when I go back to work Monday. I am pretty confident
that it will work. Thanks for the help!
http://msdn.microsoft.com/workshop/a...taoverview.asp

The example below is very basic but should give you an idea of what it
does.
if VerifyData()=true then

set dbEng = createobject("dao.dbengine.36")
dbEng.systemdb = "pathtoworkgroupfile"
dbEng.defaultuser = "username"
dbEng.defaultpassword = "password"

set wrksp = dbEng.workspaces(0)

set db = wrksp.opendatabase("PathToDataBase",false,false)

Nov 13 '05 #10

This discussion thread is closed

Replies have been disabled for this discussion.