A
AJ B
I have many different AS400's to connect to and gather information from. The
queries work with the file dsn's but after running the first ten or so in
succession the ODBC call fails. Here is that code
Dim i As Integer
Dim SQL As String
Dim curdate As Date
Dim boxnames As Variant
boxnames = Array(4, 10, 20, 21, 28, 44, 61, 66, 79, 82, 83, 90, 91, 98)
For i = 0 To 13
MsgBox ("Running query for Boxnumber = " & boxnames(i) & " .")
DoCmd.OpenQuery "BOX" & boxnames(i) & "APPEND", , acReadOnly
DoCmd.Close acQuery, "BOX" & boxnames(i) & "APPEND", acSaveNo
Next i
I think this is because although the query is closed the connection to the
database is still open. In an effort to avoid this I am trying to create a
pass through connection in my VB module so that I can control when my
connection to an AS400 database opens and closes. Now I am getting the error
provider not found when I try to open the connection. Here is my code
Dim AS400_ADOODBC As ADODB.Connection
Dim strconn As String
strconn = "Provider=MSDASQL;DRIVER=HiTODBC/400 Optimized
32bit;UID=me;DPC=0;SSL=0;ChCtg=0;CchUI=0;UnqTb=0;HoldC=1;RmBlk=1;BlkSz=512;BufSz=32767;Rows=0;Libs=0;CSID=0;Stats=1;SpCl=0;Hgls=1;TmOut=30;NtNm=APPN;MdNm=QSERVER;CmtLvl=0;RdOy=0;SDK=0;Pkg=1,1,<Default>/<Default>;CdPg=037;IPAdr=companyip;APPCRtr=10;Server=companyserv;" & _
"Database=mylib1,mylib2,mylib3,mylib4;PWD=mine"
Set AS400_ADOODBC = New ADODB.Connection
MsgBox "Connection is " & AS400_ADOODBC.Provider
AS400_ADOODBC.ConnectionString = strconn
AS400_ADOODBC.Open
Any help is appreciated.
queries work with the file dsn's but after running the first ten or so in
succession the ODBC call fails. Here is that code
Dim i As Integer
Dim SQL As String
Dim curdate As Date
Dim boxnames As Variant
boxnames = Array(4, 10, 20, 21, 28, 44, 61, 66, 79, 82, 83, 90, 91, 98)
For i = 0 To 13
MsgBox ("Running query for Boxnumber = " & boxnames(i) & " .")
DoCmd.OpenQuery "BOX" & boxnames(i) & "APPEND", , acReadOnly
DoCmd.Close acQuery, "BOX" & boxnames(i) & "APPEND", acSaveNo
Next i
I think this is because although the query is closed the connection to the
database is still open. In an effort to avoid this I am trying to create a
pass through connection in my VB module so that I can control when my
connection to an AS400 database opens and closes. Now I am getting the error
provider not found when I try to open the connection. Here is my code
Dim AS400_ADOODBC As ADODB.Connection
Dim strconn As String
strconn = "Provider=MSDASQL;DRIVER=HiTODBC/400 Optimized
32bit;UID=me;DPC=0;SSL=0;ChCtg=0;CchUI=0;UnqTb=0;HoldC=1;RmBlk=1;BlkSz=512;BufSz=32767;Rows=0;Libs=0;CSID=0;Stats=1;SpCl=0;Hgls=1;TmOut=30;NtNm=APPN;MdNm=QSERVER;CmtLvl=0;RdOy=0;SDK=0;Pkg=1,1,<Default>/<Default>;CdPg=037;IPAdr=companyip;APPCRtr=10;Server=companyserv;" & _
"Database=mylib1,mylib2,mylib3,mylib4;PWD=mine"
Set AS400_ADOODBC = New ADODB.Connection
MsgBox "Connection is " & AS400_ADOODBC.Provider
AS400_ADOODBC.ConnectionString = strconn
AS400_ADOODBC.Open
Any help is appreciated.