ODBC Connection Failing on Second Attempt

[Elysynn]

Hello,

I am making progress on a module that I am using to connect to 4 separate Sybase DB servers. Here is the problem I am running into now. After I update the registry to run the next set of queries I get an ODBC call failed message. The ODBC connection that needs to be used opens an .exe file to create the connection. I've tried closing the window to allow the next connection to be made, and this has not remedied the issue. If I connect to one Sybase server, run a query and close the window then try to run the same query I get the same ODBC Call failed message. The only way I have found to fix this so far is to close the database and start over.

The registry update will occur - it's just not registering in Access on the ODBC Call. Below is the code I am using. If you have any comments or suggestions, I would greatly appreciate it.

-Elysynn

Public Sub WitnessExtract()

Dim datDay As String
Dim datMonth As String
Dim datYear As String
Dim datExport As String
Dim strExportFile As String
Dim WshShell
Dim bKey
Dim datCurTime As Date
Dim datFutTime As Date


datDay = Format(DateSerial(Year(Date), Month(Date), Day(Date) - 1), "dd")
datMonth = Format(DateSerial(Year(Date), Month(Date), Day(Date) - 1), "mm")
datYear = Format(DateSerial(Year(Date), Month(Date), Day(Date) - 1), "yyyy")

datExport = datYear & datMonth & datDay

Set WshShell = CreateObject("WScript.Shell")
WshShell.RegWrite "HKLM\Software\ODBC\ODBC.INI\witness\EngineName", "SERVER001", "REG_SZ"
WshShell.RegWrite "HKLM\Software\ODBC\ODBC.INI\witness\Start", "dbclient.exe -c -ga -x tcpip{HOST=SERVER001}", "REG_SZ"

    DoCmd.TransferText acExportDelim, "WitnessCCR", "ex_qryDBA_agent", "U:\TransferTest\blm_agent_" & datExport & ".txt", -1
    DoCmd.TransferText acExportDelim, "WitnessCCR2", "ex_qryDBA_userid", "U:\TransferTest\blm_userid_" & datExport & ".txt", -1
    DoCmd.TransferText acExportDelim, "WitnessCCR3", "ex_qryDBA_EvalHeadSummData129", "U:\TransferTest\blm_EvalHeadSummData129_" & datExport & ".txt", -1
    DoCmd.TransferText acExportDelim, "WitnessCCR4", "ex_qryDBA_EvalQuesAnswerData129", "U:\TransferTest\blm_EvalQuesAnswerData129_" & datExport & ".txt", -1

Call fCloseApp("WATCOM SQL Network Requestor")


Set WshShell = CreateObject("WScript.Shell")
WshShell.RegWrite "HKLM\Software\ODBC\ODBC.INI\witness\EngineName", "SERVER002", "REG_SZ"
WshShell.RegWrite "HKLM\Software\ODBC\ODBC.INI\witness\Start", "dbclient.exe -c -x tcpip{HOST=SERVER002}", "REG_SZ"

datCurTime = Now
datFutTime = DateAdd("s", 10, curTime)

While datCurTime < futTime
    datCurTime = Now
Wend

    DoCmd.TransferText acExportDelim, "WitnessCCR", "ex_qryDBA_agent", "U:\TransferTest\tmp_agent_" & datExport & ".txt", -1
    DoCmd.TransferText acExportDelim, "WitnessCCR2", "ex_qryDBA_userid", "U:\TransferTest\tmp_userid_" & datExport & ".txt", -1
    DoCmd.TransferText acExportDelim, "WitnessCCR5", "ex_qryDBA_EvalHeadSummData20", "U:\TransferTest\tmp_EvalHeadSummData20_" & datExport & ".txt", -1
    DoCmd.TransferText acExportDelim, "WitnessCCR4", "ex_qryDBA_EvalQuesAnswerData20", "U:\TransferTest\tmp_EvalQuesAnswerData20_" & datExport & ".txt", -1
    
MsgBox "Done."

 

[PHV]

Just a note for a neater pause loop:
While datCurTime < futTime
datCurTime = Now
DoEvents
Wend

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886