vbs to write to mySQL on remote computer

[ChrisP]

Hi guys having a little trouble connecting to a database i have moved from access to mySQL.

When a user logs on I want to add an entry to the database via a vbs executed on the client PC. I used to do this with VBS & Access no problems but i'm scratching my head with VBS & mySQL.

So.. how would i open a connection to the "logons" table in the following example?

The myODBC name is "MYTEST" and the database is called "LOGONTEST" and they live on a server called "DBSVR1"

[Lithium]

Dim mysql, adocon

mysql = "Driver={MySQL ODBC 3.51 Driver}; Server=DBSVR1; Database=LOGONTEST; UID=<MyUser>; PWD=<MyPWD>"

Set adocon = CreateObject("ADODB.Connection")

#--- Run query
adocon.Open MyQuery, mysql

#--- update something?
adocon.Open mysql
adocon.ExecuteQuery(MyQuery)

adocon.close



Possibly. I'm not 100% if it's correct, I've just blagged that out of an ASP.NET VB project I'm working on.

I'm pretty sure they are similar. I may have some commands wrong. Google knows all if I am. (One day I'll be better than Google... but until then...)

[altecsole]

We use a MySQL database for recording logons. We record the computer and username details to a table. Assuming that your logons table has two fields called computer and user; the following should work (it's been edited from my own script, but I'm pretty sure it's correct).

Option Explicit

Dim WshNetwork
Dim strComputer
Dim strUser
Dim objDB
Dim objDBConn

Set WshNetwork = WScript.CreateObject("WScript.Network")
' GET COMPUTER AND USERNAME
strComputer = WshNetwork.ComputerName
strUser = WshNetwork.UserName

Set objDB = CreateObject("ADODB.Connection")
objDBConn = "driver={MySQL ODBC 3.51 Driver};server=DBSVR1;database=LOGONTEST;Uid=<USER _NAME>;Pwd=<PASSWORD>"

' OPEN CONNECTION TO DATABASE
objDB.Open objDBConn

Dim strSQL
strSQL = "INSERT INTO logons (computer, user)" & _
" VALUES ('" & strComputer & "','" & strUser & "')"
objDBConn.Execute strSQL
objDB.Close
Set WshNetwork = Nothing
Set objDB = Nothing

[ChrisP]

OK the nightmare is real

why it was'nt working was because you must have the myODBC driver on all clients :O OH NO!! (yes I know i could use group policy)

So i've changed the script to use (Microsoft.XMLHTTP) to do a silent post to a form on a webserver that has myODBC and a DSN connection to the database

talk about a pain in the A.

[altecsole]

The MySQL ODBC driver comes as an msi and installs via group policy without any problems. Takes about 5 seconds.