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=; PWD="

 

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=;Pwd="

 

' 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 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.