ODBC from VBS

[zain2222]

This is my first attempt at modifying existing VB Script (worked with korn shells before).
Do I need MS Visual studio for debugging? I just copied the script and use script's log file for debugging.

I am trying to modify VB script that accesses following databases on Windows 10 using ODBC:
1) MS Access for Microsoft 365 MSO 32 bit
2) Oracle 19 version 32 bit

VB script code is called from .bat script and is currently running fine on another machine and accessing table.
I got copy of VB Script and MS Access database file locally on "C:\Users\{my_user_id}\code_folder"

Oracle has client installed (also use sql developer to access Oracle and it works fine).

I did following before I start making changes to check if connection and code works okay:
- Created 32 bit ODBC connection for Oracle -I tested this connection from ODBC "Test Connection" with user id/pw and works fine
- As I understand, unlike Oracle, there is no need to create separate DSN for MS Access driver and it can be directly accessed from VB script via variable for instance Const ACCESS_DR = "{Microsoft Access Driver (*.mdb, *.accdb)}" )

Part of this code is "cofig.ini" file that contains Oracle DSN name, user id, pw and Access database name as Access DSN.
Script creates log file, which helps me find the error messages.

After all this setup, and before I make any changes to code, whenever I run, I get errors out for ALL the connections (Access and Oracle) with code "-2147467259" and message "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified".

I wanted to see if the values are being passed correctly. I did "WriteLine" to log file and check the variable values correctly populated for DSN, user id and password so "config.ini" file is passing values correctly.

My suspicion is that script is unable to "see" odbc connection.
I tested Oracle DSN with "tnsping" from "C:\Users\{my_user_id}\code_folder" command line and it pings alright.

I could not see why locally available Access DB isn't accessible.
I checked the path leading to the database and It opens up the database.

I am puzzled.
Any suggestion is welcome!

Below are scripts used. I only have partial VB script "Test.vbs" below containing connection code.
----------- Code snippet below ---------

====== start of Bat script ================
@echo off
for /F "tokens=1-3 delims=/ " %%f in ('date /t') do (
set dd=%%f
set mm=%%g
set yy=%%h
)
set yy=%yy:~-4%
set curDate=%yy%-%mm%-%dd%

for /F "tokens=1-2 delims=: " %%f in ('time /t') do (
set hh=%%f
set mm=%%g
)
set curTime=%hh%-%mm%
@echo on
cscript.exe TEST.vbs
set errorcode=%errorlevel%

if %errorcode%==0 goto endbat else goto failbat


:failbat
exit 1

===================== END OF BAT SCRIPT ===============================================

============ Start of VB Script ===============================

Const ACCESS_DRVR = "{Microsoft Access Driver (*.mdb, *.accdb)}"
Const FOR_READING = 1
Const FOR_WRITING = 2
Const FOR_APPENDING = 8


Dim g_objFSO, g_objLogFile, g_LogFolder, g_AttFileName, g_Html_FileName, g_ReportFolder, g_SrcFolder, g_CurrDir, g_DBFolder
Dim g_LoadId, g_LoadNum, g_RollbackFlg, g_EtlProcWID, g_strReport,g_Date,
Dim g_ConnACC, g_ConnTEST, g_AccDBDrvr, g_AccDB, g_AccUID, g_AccPWD, g_TstDSN, g_TstUID, g_TstPWD, g_From, g_ToList, g_SMTPSrvr
ReDim g_arrTrimTxtFile(-1)
Dim g_arrConfigFile

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ End of Global Declarations ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Sub Routines ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
initProc
Call exitProc (0, "success")
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ End of Sub Routines ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Sub Routine Definition ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Private Sub initProc
On Error Resume Next

Dim currDir, strTime, logFolder, logFileName

Set g_objFSO = CreateObject("Scripting.FileSystemObject")

g_CurrDir = g_objFSO.GetAbsolutePathName(".")
g_LogFolder = g_CurrDir & "\log"
g_ReportFolder = g_CurrDir & "\reports"
g_SrcFolder = g_CurrDir & "\files"
g_DBFolder = g_CurrDir & "\db"

strTime = Now
If Not g_objFSO.FolderExists(g_LogFolder) Then g_objFSO.CreateFolder(g_LogFolder)
g_Date = Year(strTime)& Right(Month(strTime)+100,2) & Right(Day(strTime)+100,2)

logFileName = "\DataReconciliation" & g_Date & ".log"
'Right(Hour(strTime)+100,2)& Right(Minute(strTime)+100,2) & ".log"


Set g_objLogFile = g_objFSO.OpenTextFile(g_LogFolder & LogFileName, FOR_APPENDING, True)

If Not g_objFSO.FolderExists(g_SrcFolder) Then
g_objLogFile.WriteLine (Now & vbtab & "No Source Files found in '\files'. Terminating the process")
WScript.Quit
End If

If Not g_objFSO.FolderExists(g_ReportFolder) Then g_objFSO.CreateFolder(g_ReportFolder)

g_objLogFile.WriteLine (Now & vbtab & "reading config file")

readConfigFile("config.ini")

g_From = getConfigValue("EMAIL_FROM")
g_ToList = getConfigValue("EMAIL_TO")
g_SMTPSrvr = getConfigValue("EMAIL_SMTP")


g_AccDB = getConfigValue("ACCESS_DSN")
g_AccUID = getConfigValue("ACCESS_UID")
g_AccPWD = Decrypt(getConfigValue("ACCESS_PASS"))

g_AccDBDrvr = "Driver=" & ACCESS_DRVR & ";DBQ=" & g_DBFolder & "\" & g_AccDB & ";Uid=" & g_AccUID & ";Pwd=" & g_AccPWD & ";"
g_objLogFile.WriteLine (Now & vbtab & "Test " & ACCESS_DRVR & " " & g_DBFolder & "\" & g_AccDB & ";Uid=" & g_AccUID & ";Pwd=" & g_AccPWD & " " )

g_TstDSN = getConfigValue("TEST_DSN")
g_TstUID = getConfigValue("TEST_UID")
'g_TstPWD = Decrypt(getConfigValue("TEST_PASS"))
g_TstPWD = getConfigValue("TEST_PASS")

Set g_ConnACC = CreateObject("ADODB.Connection")
Set g_ConnTEST = CreateObject("ADODB.Connection")

'g_objLogFile.WriteLine (Now & vbtab & "AccessDB - Connecting..")
g_ConnACC.Open g_AccDBDrvr

If Err.Number <> 0 Then
g_objLogFile.WriteLine (Now & vbtab & "Failed to connect to Access - " & Err.Description)
g_objLogFile.WriteLine (Now & vbtab & " " & g_AccDBDrvr & ">>>> Access Error_Number: " & Err.Number & " " )
Call exitProc(-1,"Error Connecting to Access-" & Err.Description)
Else
Err.Clear
g_objLogFile.WriteLine (Now & vbtab & "Access - Connection Established")
End If


'g_objLogFile.WriteLine (Now & vbtab & "TEST - Connecting..")
g_ConnTEST.Open g_TstDSN,g_TstUID,g_TstPWD

If Err.Number <> 0 Then
g_objLogFile.WriteLine (Now & vbtab & "Failed to connect to TEST DB - " & Err.Description)
g_objLogFile.WriteLine (Now & vbtab & "Test " & g_TstDSN & " " & g_TstUID & " " & g_TstPWD & "TEST Error_Number: " & Err.Number & " " )
Call exitProc(-1,"Error Connecting to TEST" & Err.Description)
Else
Err.Clear
g_objLogFile.WriteLine (Now & vbtab & "TEST - Connection Established")
End If

============================================END of VB Script snippet ==========================================================================

 

[strongm]

Instead of

[tt]cscript.exe TEST.vbs[/tt]

try

[tt]%windir%\syswow64\cscript.exe Test.vbs[/tt]

 

[xwb]

You can use visual studio for debugging if you wish. You can start the application and attach to mshta specifying script instead of native.

 

[zain2222]

@strongm (MIS)
"%windir%\syswow64\cscript.exe Test.vbs"
Worked!!! Thank you very much. I can now be connected to Access db but Oracle connection via dsn still doesnt connect.
Any idea ?

I tried following since Oracle is 32 bit driver just to see but then Access did not work nor Oracle. "%windir%\system32\cscript.exe Test.vbs"

Following is Oracle connection configuration:

ODBC
-----
DNS name is defined as TEST_DSN in ODBC and TNS Service Name as "testtnsname".

Content of Cofig.ini
-------------
TEST_DSN=testtnsname
TEST_UID=testloginuserid
TEST_PASS=testpassword

g_TstDSN = getConfigValue("TEST_DSN")
g_TstUID = getConfigValue("TEST_UID")
g_TstPWD = getConfigValue("TEST_PASS")

-----------------------------------------------------------------



@xwb (Programmer)
For MSHTA, do you have any docs I am unfamiliar with it.

 

[zain2222]

MS Access connection is working fine.
Kind of passed through the previous Oracle connection error but got different Oracle connection error now.
Since DSN was defined in ODBC as TEST_DSN, to resolve previous error, I referred to DSN instead of tnsname (like below).

Content of Cofig.ini
-------------
TEST_DSN=TEST_DSN
TEST_UID=testloginuserid
TEST_PASS=testpassword

I will look into it how to resolve new Error but if someone knows, please share:

New Error: "The server rejected one or more recipient addresses. The server response was: ### #.#. <myfirst.lastname@company.com>... Relaying denied. IP name lookup failed [###.##.###.#]"

 

[zain2222]

Last error resolved and now I can connect to Oracle db.
Reason for last error was call to email function to send email with information when error occurs.
I believe Email functionality for SMTP is either not setup right from my id or port is blocked, and not allowing me to send error email.

Thank you for all your help and

 

[iambob]

These answers look good, and my advice is try the Microsoft: Office forums for anymore help with Windows programming.

Sincerely,
Bob Space

 

[strongm]

>my advice is try the Microsoft: Office forums for anymore help with Windows programming.

This is exactly the right forum for a VBScript question. Why would you send someone to the Office forum(s) instead?

 

[iambob]

They were wondering about office. It was somewhere in the thread.

Sincerely,
Bob Space

 

[strongm]

The first mention of Office in this thread was you.

The closest the OP comes is in mentioning MS Access for Microsoft 365 MSO, but that is simply to give us context (that's the software they used to create the database, in case that is relevant to the connection string they need to use in their script). They also mention Oracle, but again this is not an Oracle question

It's great that you are trying to help, but in doing so it is important to try and understand the question being asked (and we all get that wrong from time to time!)