MS Access97

Forums

My Profile

Inbox

Address Book

My Subscription

My Forums

MS Access97

Logged in as: Guest

arrSession:exec spGetSession 2,2,31191

	Active Users: There are 0 members and 0 guests.
	Users viewing this topic: none

Printable Version

All Forums >> [Scripting] >> WSH & Client Side VBScript >> MS Access97
Do you like VisualBasicScript.com? Link to us and help spread the word about our forum. Thanks!

Page: [1]

Message

<< Older Topic Newer Topic >>

MS Access97 - 2/16/2006 4:29:34 PM

mojeaux65

Posts: 7
Score: 0
Joined: 2/16/2006
Status: offline

Hi All,
I'm looking for a point in the right direction, more or less. I am wanting to add/update/delete records in an Access97 database from an HTML page(non ASP). My co-worker reassures me it can be done using VBScript client side.

I've looked through the forum, but haven't found anything close to what I'm looking for. So I have a couple questions....

Have any of the modarators had any success with this? (would like to verify it actually can be done)
Can you suggest any websites which provide tutorials/sample scripts/snippets so I can get a better grasp on what will be needed. (anything you may have found helpful)

Thank you for your time!

Post #: 1

RE: MS Access97 - 2/17/2006 12:46:06 AM

ginolard

Posts: 1082
Score: 21
Joined: 8/10/2005
Status: offline

Do a search on ADODB and/or "Microsoft.Jet.OLEDB.4.0"

_____________________________

Author of ManagePC - http://managepc.net
AD Query Template - http://www.visualbasicscript.com/m_40609/tm.htm
Consolidated Scripting Framework - http://www.visualbasicscript.com/m_59109/tm.htm

(in reply to mojeaux65)

Post #: 2

RE: MS Access97 - 2/17/2006 1:10:48 AM

Country73

Posts: 735
Score: 10
Status: offline

Is this what you were looking for:
Web Publishing with Microsoft Access 97

(in reply to mojeaux65)

Post #: 3

RE: MS Access97 - 2/17/2006 7:33:18 AM

mojeaux65

Posts: 7
Score: 0
Joined: 2/16/2006
Status: offline

Thank you for the helpful points in the right direction.

I've been out visiting some of the recommended sites and came up with a somewhat simple script to add a new record to a database from an html page:

<html>

<head>
<SCRIPT language="VBScript">

Dim dbMyDB As Database
Dim rsMyRS As RecordSet
Dim rsName, rsEmail, rsPhone, rsStatus, rsDate, rsNote as String

Set dbMyDB = OpenDatabase("\\server\TESTDB\PROJ1.MDB")
Set rsMyRS = dbMyDB.OpenRecordSet("Proj1", dbOpenDynaset)
Set rsName = myform.Name.value
Set rsEmail = myform.Email.value
Set rsPhone = myform.Phone.value
Set rsStatus = myform.Status.value
Set rsDate = myform.Date.value
Set rsNote = myform.Note.value

Sub Add_newrec()
rsMyRS.AddNew
rsMyRS = rsName
1stRecords.AddItem rsMyRS!Name
1stRecords.ItemData(1stRecords.NewIndex) = rsMyRS!ID
rsMyRS!Phone = rsPhone
rsMyRS.Update
End Sub

</Script>
</head>

<body>
<form name="myform">

Name: <input type="text" name="Name" size="50"><br>

E-mail: <input type="text" name="Email" size="50"><br>

Phone: <input type="text" name="Phone" size="50"><br>

Status: <input type="text" name="Status" size="50"><br>

Date: <input type="text" name="Date" size="50"><br>

Note: <input type="text" name="Note" size="50"><br>

<input type="submit" value="Submit" name="B1" onClick="Add_newrec()"><input type="reset" value="Reset" name="B2">
</form>

</body>

</html>

However, I am getting an error message - " Expected End of Statement". I'm I missing some syntax such as a semi-colon, etc?
Thank you!

(in reply to mojeaux65)

Post #: 4

RE: MS Access97 - 2/17/2006 7:55:34 AM

ebgreen

Posts: 5246
Score: 31
Joined: 7/12/2005
Status: offline

Well for one thing, you cannot use strong typing with VBScript, so remove the "As String" from your Dim line. Other than that, I don't see anything glaring. BTW, block indentation is your friend. Here is your code with the As String removed and formatted so that it is easier to read:

<html>
     <head>
         <SCRIPT language="VBScript">
             Dim dbMyDB As Database
             Dim rsMyRS As RecordSet
             Dim rsName, rsEmail, rsPhone, rsStatus, rsDate, rsNote
             
             Set dbMyDB = OpenDatabase("\\server\TESTDB\PROJ1.MDB")
             Set rsMyRS = dbMyDB.OpenRecordSet("Proj1", dbOpenDynaset)
             Set rsName = myform.Name.value
             Set rsEmail = myform.Email.value
             Set rsPhone = myform.Phone.value
             Set rsStatus = myform.Status.value
             Set rsDate = myform.Date.value
             Set rsNote = myform.Note.value
             
             Sub Add_newrec()
                 rsMyRS.AddNew
                 rsMyRS = rsName
                 1stRecords.AddItem rsMyRS!Name
                 1stRecords.ItemData(1stRecords.NewIndex) = rsMyRS!ID
                 rsMyRS!Phone = rsPhone
                 rsMyRS.Update
             End Sub
         </Script>
     </head>
     <body>
         <form name="myform">
             Name: <input type="text" name="Name" size="50"><br>
             E-mail: <input type="text" name="Email" size="50"><br>
             Phone: <input type="text" name="Phone" size="50"><br>
             Status: <input type="text" name="Status" size="50"><br>
             Date: <input type="text" name="Date" size="50"><br>
             Note: <input type="text" name="Note" size="50"><br>
             <input type="submit" value="Submit" name="B1" onClick="Add_newrec()">
             <input type="reset" value="Reset" name="B2">
         </form>
     </body>
 </html>

_____________________________

"... when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick
Goog places to start:http://www.visualbasicscript.com/m_24727/tm.htm
http://www.visualbasicscript.com/m_47117/tm.htm

(in reply to mojeaux65)

Post #: 5

RE: MS Access97 - 2/17/2006 10:45:24 AM

mojeaux65

Posts: 7
Score: 0
Joined: 2/16/2006
Status: offline

Thank you for the reformatting tip. Admittedly, I am a closet slob when it comes to code. Just run everything left justified and separate where needed with a line break.

I implemented the recommended changes, but getting the same error. I also removed the typing on dbMYDB and rsMyRS... but no luck.

Does it matter that the db and html page reside on a data server and not a web server?

oh and off topic... you're signature is great! lol

Thx again!

(in reply to ebgreen)

Post #: 6

RE: MS Access97 - 2/17/2006 11:10:22 AM

ebgreen

Posts: 5246
Score: 31
Joined: 7/12/2005
Status: offline

The next thing I would question is the use of the ! accessor. I believe that is a VBA construct and not valid in VBS. As a matter of fact I think most of your code is VBA not VBScript. I would suggest searching this forum or the web for examples of using VBScript to manipulate an access db.

_____________________________

"... when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick
Goog places to start:http://www.visualbasicscript.com/m_24727/tm.htm
http://www.visualbasicscript.com/m_47117/tm.htm

(in reply to mojeaux65)

Post #: 7

RE: MS Access97 - 2/18/2006 6:57:21 AM

mojeaux65

Posts: 7
Score: 0
Joined: 2/16/2006
Status: offline

Ok, made another pass at this... but I have several questions, if you don't mind.... I have added them where they apply in the script.. please see below.

<html>

<head>
<SCRIPT language="VBScript">

Sub Add_newrec()

'In this sample vbscrption, no variables were declared... do I need to do that? If so, do I declare all of them?

'What are these(what do they do for the user)?
    Const adOpenStatic = 3
    Const adLockOptimistic = 3
    Const adUseClient = 3

    Set objConnection = CreateObject("ADODB.Connection")
    Set objRecordset = CreateObject("ADODB.Recordset")

'Using this configuration, Do I include the path names to the database? and should I use the .mdb extension?
    objConnection.Open "DSN=\\dataserver name\TESTDB\Proj1;"

'What does cursorlocation do? Is it required in adding/updating records in the db?
    objRecordset.CursorLocation = adUseClient

'Do I have to run a query before adding a record? If I just want to add a record to table,
'can I just open the table? Do you have the syntax for this?
    objRecordset.Open "SELECT * FROM Hardware" , objConnection, adOpenStatic, adLockOptimistic
    Set colSoundCards = GetObject("winmgmts:").ExecQuery("Select * from Win32_SoundDevice")

'Do I have to declare the text box values or can I just point to the value in the form?
    objRecordset.AddNew
    objRecordset("Name") = myform.name.Value
objRecordset("Email") = myform.Email.Value
    objRecordset("Phone") = myform.Phone.Value
    objRecordset("Status") = myform.Status.Value
    objRecordset("Date") = myform.Date.Value
    objRecordset("Note") = myform.Note.Value
    objRecordset.Update

   objRecordset.Close
   objConnection.Close

End Sub

</Script></head>

<body>
<body>

<form name="myform">

Name: <input type="text" name="Name" size="50"><br>

E-mail: <input type="text" name="Email" size="50"><br>

Phone: <input type="text" name="Phone" size="50"><br>

Status: <input type="text" name="Status" size="50"><br>

Date: <input type="text" name="Date" size="50"><br>

Note: <input type="text" name="Note" size="50"><br>

<input type="submit" value="Submit" name="B1" onClick="Add_newrec()">

<input type="reset" value="Reset" name="B2">

</form>

</body>

</body>

</html>

Alot of questions, I know!   I am truly a newbie at this.