asked on

VBScript write to ODBC database on remote machine

Using VBScript I need to write data to an Access database on a remote machine.

At the moment, I have the script below, which works perfectly, but requires me to manually set up the ODBC first.

What is the best way to automate being able to write to the remote database?

500 points, because it is fairly urgent.

Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adUseClient = 3
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordset = CreateObject("ADODB.Recordset")
 
objConnection.Open "DSN=Test;"
objRecordset.CursorLocation = adUseClient
 
strIPaddr = "10.0.0.1"
strComputerName = "test"
strSerialNumber = "90556"
strLocation = "administration"
strYear = "2009"
strInformation = "This is a test to see if the script works"
 
objRecordset.Open "SELECT * FROM Computers" , objConnection, adOpenStatic, adLockOptimistic
 
   objRecordset.AddNew
 
    objRecordset("ComputerName") = strComputerName
    objRecordset("YearAcquired") = strYear
    objRecordset("Location") = strLocation
    objRecordset("Information") = strInformation
    objRecordset("SerialNumber") = strSerialNumber
 
    objRecordset.Update
 
objRecordset.Close
objConnection.Close

Open in new window

Jim P.

SQL is easy, Access is harder because of the three level key.

The best way I can think to do it is export the HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI registry to a file and edit to look something like below.

Then save it on the network as MyAcc.REG

Then in your script have a command
----------------------------------------------------------
REGEDIT -S \\MyServer\MyShare\MyAcc.REG
----------------------------------------------------------
to load it on a machine.

Windows Registry Editor Version 5.00
 
[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources]
"Xtreme Sample Database 10"="Microsoft Access Driver (*.mdb)"
 
[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\Xtreme Sample Database 10]
"Driver"="C:\\WINDOWS\\System32\\odbcjt32.dll"
"DBQ"="Q:\\Program Files\\Crystal Decisions\\Crystal Reports 10\\Samples\\En\\Databases\\xtreme.mdb"
"Description"="Database for Crystal Reports Samples"
"DriverId"=dword:00000019
"FIL"="MS Access"
"PWD"=""
"SafeTransactions"=dword:00000000
"UID"=""
 
[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\Xtreme Sample Database 10\Engines]
 
[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\Xtreme Sample Database 10\Engines\Jet]
"Driver"="odbcjt32.dll"
"ImplicitCommitSync"=""
"Threads"=dword:00000003
"UserCommitSync"="Yes"

Open in new window

ASKER CERTIFIED SOLUTION

DanRollins

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

churchlandsshs

ASKER

Used my script, but changed the objConnection.Open command to:
objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\server\share\folder\database.mdb"