automation connection to Db2 tables

G

Guest

I have an app with Odbc connection to Db2 tables.
To open the connection I do have to give a UserID and Password to get through.
Is there anyone who can help me out on this, to make this process run on its
own.
Find hereafter some script, but I'll still encounter the fill-in box for
UserID and Password.

Option Compare Database
Option Explicit

Dim sqlDb2, sqlCCTab, sqlCmd As String
Dim db2cnstr As String

Dim odbcdsn As String
Dim UserID As String
Dim Passwd As String
Dim Host As String
Dim serverdsn As String
Dim portnumber As Integer

Dim db2con As New ADODB.Connection
Dim cnn As New ADODB.Connection
Dim rs_Db2 As New ADODB.Recordset
Dim rs_CCTab As New ADODB.Recordset

Private Sub Form_Open(Cancel As Integer)


odbcdsn = "SequelinktoDB2G1"
UserID = "anyUser"
Passwd = "SomePasswd"
Host = "ibm.agfa.be"
serverdsn = "TCPtoMVSDB2G1"
portnumber = 3008

'Opening the tables (Recordsets)

' 'Define connection to an outside database
' cnnstr = "Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;"
' cnnstr = cnnstr + "Data Source=I:\be\_agb\agfaproj\logistiek\WOS\WOS
XP_be.mdb"

'Define connection string to DB2
db2cnstr = "DSN=" & odbcdsn & ";SDSN=" & serverdsn & ";HST=" & Host &
";PRT=" & CStr(portnumber)
db2cnstr = db2cnstr & ";UID=" & UserID & ";PWD=" & Passwd

Set db2con = New ADODB.Connection
db2con.ConnectionString = db2cnstr

'Table 1
'Define rs_Db2
sqlDb2 = "SELECT * FROM VGEGD.EUY9A_V001"
'Open connection to the database
db2con.Open
DoCmd.TransferDatabase acLink, "ODBC", db2cnstr & "VGEGD_EUY1S_V001"
Set rs_Db2 = db2con.Execute(sqlDb2)

Set cnn = CurrentProject.Connection
'Table 2
sqlCCTab = "Abec05"
rs_CCTab.Open sqlCCTab, cnn, adOpenStatic, adLockOptimistic

rs_Db2.MoveFirst
sqlCmd = "SELECT [CLAND] & [CLN4AF] AS land, Abec05.CBUSIU AS BU,
Abec05.CSTBUD AS BC,"
sqlCmd = sqlCmd & "Abec05.CBAPG AS PG, Abec05.CBAVSS AS VSS,
Abec05.CAFRKL AS Referentie,"
sqlCmd = sqlCmd & "Abec05.CODID AS [O/R/D], Abec05.COESTA AS Status,
Abec05.CORARK AS MABC,"
sqlCmd = sqlCmd & "Abec05.EBAONS AS Omschrijving, Abec05.DORTO5 AS
[Datum toekenning],"
sqlCmd = sqlCmd & "Abec05.ROEVK5 AS [Aantal toegekend], Abec05.CORWMS AS
Gebouw, Abec05.CALAS1 AS CTAS,"
sqlCmd = sqlCmd & "IIf(IsNull([dorto5]),0,Date()-CDate(Mid([DORTO5],7,2)
& '" & " / "
sqlCmd = sqlCmd & "' & Mid([dorto5],5,2) & '" & " / " & "' &
Mid([dorto5],1,4))) AS [Aantal dagen toegekend],"
sqlCmd = sqlCmd & "Abec05.DZEWZ4 AS [Datum verzendklaar],
IIf(IsNull([DZEWZ4]),0,Date()-CDate("
sqlCmd = sqlCmd & "Mid([DZEWZ4],7,2) & '" & " / " & "' &
Mid([DZEWZ4],5,2) & '" & " / " & "' & Mid([DZEWZ4],1,4))) "
sqlCmd = sqlCmd & "AS [Aantal dagen verzendklaar], Date()-1=[DORTO5] AS
[Vandaag toegekend], Date() AS datum,"
sqlCmd = sqlCmd & "VGEGD_EUY1S_V001.CVSPFN AS ProdFam INTO
Tbl_2_ShippedNotSend"
sqlCmd = sqlCmd & " FROM Abec05 INNER JOIN VGEGD_EUY1S_V001 ON
(Abec05.CBAVSS = VGEGD_EUY1S_V001.MVSS) AND "
sqlCmd = sqlCmd & "(Abec05.CBAPG = VGEGD_EUY1S_V001.CPGROE)"
sqlCmd = sqlCmd & "WHERE (((Abec05.COESTA)='" & "VZ" & "') AND
((VGEGD_EUY1S_V001.DVGJR)=Year(Date())) AND "
sqlCmd = sqlCmd & "((Abec05.COESSF)<>'" & "ZZA" & "'));"

DoCmd.RunSQL sqlCmd

rs_Db2.Close
rs_CCTab.Close
Set rs_Db2 = Nothing
Set rs_CCTab = Nothing

'db2con left open, to provide all other connections to Db2 tables without
reactivating the connection
'db2con.Close
'Set db2con = Nothing

End Sub
 
A

Arvin Meyer [MVP]

Providing that the User ID and password are:

UserID = "anyUser"
Passwd = "SomePasswd"

and the connection actually used the constant:

Passwd

it should work. Perhaps it's a timing issue. Try connecting a form that
isn't bound to any data first as a startup form, then use a button on that
form to open the one with the data in it.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

Amduke said:
I have an app with Odbc connection to Db2 tables.
To open the connection I do have to give a UserID and Password to get
through.
Is there anyone who can help me out on this, to make this process run on
its
own.
Find hereafter some script, but I'll still encounter the fill-in box for
UserID and Password.

Option Compare Database
Option Explicit

Dim sqlDb2, sqlCCTab, sqlCmd As String
Dim db2cnstr As String

Dim odbcdsn As String
Dim UserID As String
Dim Passwd As String
Dim Host As String
Dim serverdsn As String
Dim portnumber As Integer

Dim db2con As New ADODB.Connection
Dim cnn As New ADODB.Connection
Dim rs_Db2 As New ADODB.Recordset
Dim rs_CCTab As New ADODB.Recordset

Private Sub Form_Open(Cancel As Integer)


odbcdsn = "SequelinktoDB2G1"
UserID = "anyUser"
Passwd = "SomePasswd"
Host = "ibm.agfa.be"
serverdsn = "TCPtoMVSDB2G1"
portnumber = 3008

'Opening the tables (Recordsets)

' 'Define connection to an outside database
' cnnstr = "Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;"
' cnnstr = cnnstr + "Data Source=I:\be\_agb\agfaproj\logistiek\WOS\WOS
XP_be.mdb"

'Define connection string to DB2
db2cnstr = "DSN=" & odbcdsn & ";SDSN=" & serverdsn & ";HST=" & Host &
";PRT=" & CStr(portnumber)
db2cnstr = db2cnstr & ";UID=" & UserID & ";PWD=" & Passwd

Set db2con = New ADODB.Connection
db2con.ConnectionString = db2cnstr

'Table 1
'Define rs_Db2
sqlDb2 = "SELECT * FROM VGEGD.EUY9A_V001"
'Open connection to the database
db2con.Open
DoCmd.TransferDatabase acLink, "ODBC", db2cnstr & "VGEGD_EUY1S_V001"
Set rs_Db2 = db2con.Execute(sqlDb2)

Set cnn = CurrentProject.Connection
'Table 2
sqlCCTab = "Abec05"
rs_CCTab.Open sqlCCTab, cnn, adOpenStatic, adLockOptimistic

rs_Db2.MoveFirst
sqlCmd = "SELECT [CLAND] & [CLN4AF] AS land, Abec05.CBUSIU AS BU,
Abec05.CSTBUD AS BC,"
sqlCmd = sqlCmd & "Abec05.CBAPG AS PG, Abec05.CBAVSS AS VSS,
Abec05.CAFRKL AS Referentie,"
sqlCmd = sqlCmd & "Abec05.CODID AS [O/R/D], Abec05.COESTA AS Status,
Abec05.CORARK AS MABC,"
sqlCmd = sqlCmd & "Abec05.EBAONS AS Omschrijving, Abec05.DORTO5 AS
[Datum toekenning],"
sqlCmd = sqlCmd & "Abec05.ROEVK5 AS [Aantal toegekend], Abec05.CORWMS
AS
Gebouw, Abec05.CALAS1 AS CTAS,"
sqlCmd = sqlCmd &
"IIf(IsNull([dorto5]),0,Date()-CDate(Mid([DORTO5],7,2)
& '" & " / "
sqlCmd = sqlCmd & "' & Mid([dorto5],5,2) & '" & " / " & "' &
Mid([dorto5],1,4))) AS [Aantal dagen toegekend],"
sqlCmd = sqlCmd & "Abec05.DZEWZ4 AS [Datum verzendklaar],
IIf(IsNull([DZEWZ4]),0,Date()-CDate("
sqlCmd = sqlCmd & "Mid([DZEWZ4],7,2) & '" & " / " & "' &
Mid([DZEWZ4],5,2) & '" & " / " & "' & Mid([DZEWZ4],1,4))) "
sqlCmd = sqlCmd & "AS [Aantal dagen verzendklaar], Date()-1=[DORTO5] AS
[Vandaag toegekend], Date() AS datum,"
sqlCmd = sqlCmd & "VGEGD_EUY1S_V001.CVSPFN AS ProdFam INTO
Tbl_2_ShippedNotSend"
sqlCmd = sqlCmd & " FROM Abec05 INNER JOIN VGEGD_EUY1S_V001 ON
(Abec05.CBAVSS = VGEGD_EUY1S_V001.MVSS) AND "
sqlCmd = sqlCmd & "(Abec05.CBAPG = VGEGD_EUY1S_V001.CPGROE)"
sqlCmd = sqlCmd & "WHERE (((Abec05.COESTA)='" & "VZ" & "') AND
((VGEGD_EUY1S_V001.DVGJR)=Year(Date())) AND "
sqlCmd = sqlCmd & "((Abec05.COESSF)<>'" & "ZZA" & "'));"

DoCmd.RunSQL sqlCmd

rs_Db2.Close
rs_CCTab.Close
Set rs_Db2 = Nothing
Set rs_CCTab = Nothing

'db2con left open, to provide all other connections to Db2 tables without
reactivating the connection
'db2con.Close
'Set db2con = Nothing

End Sub
Click to expand...
 
S

Stefan Hoffmann

hi,
I have an app with Odbc connection to Db2 tables.
To open the connection I do have to give a UserID and Password to get through.
Is there anyone who can help me out on this, to make this process run on its
own.
Find hereafter some script, but I'll still encounter the fill-in box for
UserID and Password.
Click to expand...
Use linked tables. Create an passthrough with credentials. Once opened,
all other tables with the same Connect will use it without prompting for
further credentials.

Or create an ADODB.Connection as you do in your sample, but don't use
DAO functions (.TransferDatabase).


mfG
--> stefan <--
 
G

Guest

Thank you both, Arvin and Stefan for the quick response.
The solution of Arvin is already in place. I start in a macro as first
action a form with the code in it to make the connection first. Atherwards
the querys come to run.

The solution of Stefan; All of the DB2 tables are linked.
One of the major issues is, make contact on the begin of the process and run
all neseccary querys. Last step: close the application to close also the
contact with the DB2 tables so there is no open link left.

More suggestions are welcome. Thank you.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Open recordset fails with ODBC connection 4

Top