VBA problem with ODBC

  • Thread starter Thread starter Tom
  • Start date Start date
T

Tom

I believe this is more of a VBA problem, as my ODBC code works, in
another module...

I have a module with code that I assembled last year. And my ODBC
code works. When I run the code, Access does *not* ask me for a
username & password. When the [working] ODBC code gets executed,
we've started from a macro, which executed the Public function below,
which then called another function in the same module. (The macro
here does only one thing, executes the function below. The same
function which does everything else, such as calling the other
functions, running the queries, whatever, etc.) Here.

Here's some of the code:

Public Function fncRunJobAcctsRecAging()
On Error GoTo Err_RunJobAcctsRecAging

StartTime = Time

DoCmd.Hourglass True
SysCmd acSysCmdSetStatus, ("Running Job...")
DoCmd.SetWarnings False
Set DAOdbs = CurrentDb

intPBOverallMax = 18 ' set MAX value to maximum tasks we'll
have in this module
intPBOverallCntr = 0 ' set overall counter to zero
intPBTaskMax = 18
intPBTaskCntr = 0
DoCmd.OpenForm "frmProgressBar"
Forms!frmProgressBar.Caption = "Utility Billing Accounts
Receivable by Age..."
DoEvents
 
In the first (working) instance, you have:
Set DAOdbs = CurrentDb in 1 Function and then
Set DAOdbs = OpenDatabase("", False, False, strConnect) in another.
Even tho the Function is declared Public, its variables (including object
variables) would be "private" to that Function. So the second "Set" is to a
different object than the 1st so there's no problem

In the second instance, (not working), you have both statements in the same
function. 1st you set the object to the current db, then you try to open a
second database using the same object name.

Either clear the object:

Set DAOdbs = nothing before the second assignment or change 2nd object name.

BTW => You really should properly Declare (Dim) ALL your variables

BAC
Tom said:
I believe this is more of a VBA problem, as my ODBC code works, in
another module...

I have a module with code that I assembled last year. And my ODBC
code works. When I run the code, Access does *not* ask me for a
username & password. When the [working] ODBC code gets executed,
we've started from a macro, which executed the Public function below,
which then called another function in the same module. (The macro
here does only one thing, executes the function below. The same
function which does everything else, such as calling the other
functions, running the queries, whatever, etc.) Here.

Here's some of the code:

Public Function fncRunJobAcctsRecAging()
On Error GoTo Err_RunJobAcctsRecAging

StartTime = Time

DoCmd.Hourglass True
SysCmd acSysCmdSetStatus, ("Running Job...")
DoCmd.SetWarnings False
Set DAOdbs = CurrentDb

intPBOverallMax = 18 ' set MAX value to maximum tasks we'll
have in this module
intPBOverallCntr = 0 ' set overall counter to zero
intPBTaskMax = 18
intPBTaskCntr = 0
DoCmd.OpenForm "frmProgressBar"
Forms!frmProgressBar.Caption = "Utility Billing Accounts
Receivable by Age..."
DoEvents
.
.
' go run the queries

Forms!frmProgressBar.Caption = "Retrieving 'Current' aging data
from Bill History table..."
DoEvents
strAgeGroup = "C"
Call fncSQLgetTbl420Trans
.
.
'--------------------------------------------
Private Function fncSQLgetTbl420Trans()
On Error GoTo Err_SQLgetTbl420Trans

Dim strConnect As String, strSQL As String

strLinkDSNname = "EPPDFIN"
If strLinkDSNname = "" Then
MsgBox "You must supply a DSN in order to link tables."
Else
strConnect = "ODBC;DSN=" & strLinkDSNname & ";uid=" &
strCurrentUser & _
";mode=share;dbalias=" & strLinkDSNname &
";trusted_connection=1;;"
End If
Set DAOdbs = OpenDatabase("", False, False, strConnect)
DoCmd.SetWarnings False
MsgBox strConnect, , "strConnect - " & Date & " - " & Time

SysCmd acSysCmdSetStatus, ("Running Job...")

Set DAOdbs = CurrentDb
Set DAOrs = DAOdbs.OpenRecordset("tblAcctsRecAging_Periods")

' determine the periods for which to retrieve data...
' .
.

In my current module, the one which does *not* work, I've copied the
good code, making only what I *thought* were minor changes. Here my
macro executes the function [below] with the ODBC code, with no call
to another function, as above. (The macro here runs a *lot* of
things, code, queries, etc.) Here's the code:

Public Function fncSQLgetTbl420Trans()
On Error GoTo Err_SQLgetTbl420Trans

Dim strConnect As String, strSQL As String

Set DAOdbs = CurrentDb

strLinkDSNname = "EPPDFIN"
strCurrentUser = Environ$("UserName")

If strLinkDSNname = "" Then
MsgBox "You must supply a DSN in order to link tables."
Else
strConnect = "ODBC;DSN=" & strLinkDSNname & ";uid=" &
strCurrentUser & _
";mode=share;dbalias=" & strLinkDSNname &
";trusted_connection=1;;"
End If

Set DAOdbs = OpenDatabase("", False, False, strConnect)
DoCmd.SetWarnings False
' MsgBox strConnect, , "strConnect - " & Date & " - " & Time

SysCmd acSysCmdSetStatus, ("Running Job...")

DoCmd.RunSQL ("DELETE * FROM tblRevenue_CIS_Details")

I've futsed with it again and again. Whatever *it* is, I don't see
it. And I don't understand why it won't work, seeing as it's
*supposed* to be [essentially] the same code.

If anyone has any thoughts/comments/suggestions I'd appreciate you
sharing it/them with me.

Thanks much in advance,

Tom
Click to expand...
 
I appreciate your repsonse.

I don't mean to be so...dense...about this, but I'm obviously still missing
something.

I've tried changing my code, and have had only marginal success. Here's what I
have now:

'----------------------------------------------------------------------
Option Compare Database

Public intLoop1 As Variant, intNmbrOfLoops As Variant, intNmbrOfRecs As
Variant
Public intLoop2 As Variant
Public strSQLinsert1 As String, strSQLselect1 As String, strSQLfrom1 As
String
Public strSQLwhere1 As String, strSQLwhere1a As String, strSQLorderby1 As
String
Public strSQLstatement1 As String, strAgeGroup As String
Public intPeriodYear As Variant, intPeriodMonth As Variant,
intPeriodYearPlus1 As Variant
Public strWhichAmtBilled As String, strWhichAmtUnpaid As String
Public strSQLinsert2 As String, strSQLselect2 As String, strSQLfrom2 As
String
Public strSQLon2 As String, strSQLwhere2 As String, strSQLin2 As String
Public strSQLorderby2 As String
Public strSQLstatement2 As String


Public Function fncSQLgetTbl420Trans()
On Error GoTo Err_SQLgetTbl420Trans

Dim strConnect As String, strSQL As String

SysCmd acSysCmdSetStatus, ("Running Job...")

DoCmd.RunSQL ("DELETE * FROM tblRevenue_CIS_Details")

Set DAOdbs = CurrentDb
Set DAOrs = DAOdbs.OpenRecordset("tblRevenue_CIS_Period")

' determine the periods for which to retrieve data...
'
intPeriodYear = DAOrs![PeriodYear]
intPeriodMonth = DAOrs![PeriodMonth]
intPeriodYearPlus1 = DAOrs![PeriodYearPlus1]

' Set DAOdbs = Nothing

strLinkDSNname = "EPPDFIN"
' strCurrentUser = Environ$("UserName")
If strLinkDSNname = "" Then
MsgBox "You must supply a DSN in order to link tables."
Else
strConnect = "ODBC;DSN=" & strLinkDSNname & ";uid=" & strCurrentUser & _
";mode=share;dbalias=" & strLinkDSNname &
";trusted_connection=1;;"
End If
' Set DAOdbs = CurrentDb
Set DAOdbsBE = OpenDatabase("", False, False, strConnect)

DoCmd.SetWarnings False
' MsgBox strConnect, , "strConnect - " & Date & " - " & Time

' compose SQL string 1 for the back end (BE) - DB2
'
strSQLselect1 = _
"SELECT distinct " & vbCrLf & _
" b.UTCSID," & vbCrLf & _
" b.UTLCID," & vbCrLf & _
" b.UTRCLS," & vbCrLf & _
" b.UTSVC," & vbCrLf & _
" b.UTPEYY," & vbCrLf & _
" b.UTPEMM," & vbCrLf & _
" b.UTAGE," & vbCrLf & _
" b.UTTTYP," & vbCrLf & _
" b.UTTDSC," & vbCrLf & _
" b.UTTAMT," & vbCrLf & _
" b.UTUNPD" & vbCrLf

strSQLfrom1 = _
"FROM " & vbCrLf & _
" CXLIB.UT420AP as b " & vbCrLf

strSQLwhere1 = _
"WHERE " & vbCrLf & _
" (((b.UTAGE='C ')) AND (" & _
"(((b.UTPEMM)=" & intPeriodMonth & ") AND ((b.UTPEYY)=" & intPeriodYear
& ")))) " & _
vbCrLf

strSQLorderby1 = _
"ORDER BY " & vbCrLf & _
" b.UTRCLS," & vbCrLf & _
" b.UTSVC," & vbCrLf & _
" b.UTPEYY," & vbCrLf & _
" b.UTPEMM," & vbCrLf & _
" b.UTTTYP," & vbCrLf & _
" b.UTTDSC;" & vbCrLf

strSQLstatement1 = _
strSQLselect1 & vbCrLf & _
strSQLfrom1 & vbCrLf & _
strSQLwhere1 & vbCrLf & _
strSQLorderby1

' MsgBox strSQLstatement1, , "strSQLstatement1 - " & Date & " - " & Time
DAOdbs.QueryDefs("qryRevenue_CIS_0200_BE").SQL = strSQLstatement1

' compose SQL string 2 for the front end (FE) - Access
'
strSQLinsert2 = _
"INSERT INTO tblRevenue_CIS_Details" & vbCrLf & _
"( CustID, " & vbCrLf & _
"LocID, " & vbCrLf & _
"CustClass, " & vbCrLf & _
"Serv, " & vbCrLf & _
"PeriodYear, " & vbCrLf & _
"PeriodMonth, " & vbCrLf & _
"AgeCode, " & vbCrLf & _
"ChgType, " & vbCrLf & _
"ChgDesc, " & vbCrLf & _
"AmtBilled, " & vbCrLf & _
"Unpaid, " & vbCrLf & _
"DateRetrieved ) " & vbCrLf

strSQLselect2 = _
"SELECT " & vbCrLf & _
"qryRevenue_CIS_0200_BE.UTCSID, " & vbCrLf & _
"qryRevenue_CIS_0200_BE.UTLCID, " & vbCrLf & _
"qryRevenue_CIS_0200_BE.UTRCLS, " & vbCrLf & _
"qryRevenue_CIS_0200_BE.UTSVC, " & vbCrLf & _
"qryRevenue_CIS_0200_BE.UTPEYY, " & vbCrLf & _
"qryRevenue_CIS_0200_BE.UTPEMM, " & vbCrLf & _
"qryRevenue_CIS_0200_BE.UTAGE, " & vbCrLf & _
"qryRevenue_CIS_0200_BE.UTTTYP, " & vbCrLf & _
"qryRevenue_CIS_0200_BE.UTTDSC, " & vbCrLf & _
"qryRevenue_CIS_0200_BE.UTTAMT, " & vbCrLf & _
"qryRevenue_CIS_0200_BE.UTUNPD, " & vbCrLf & _
"Now() AS Expr1 " & vbCrLf

strSQLfrom2 = _
"FROM " & vbCrLf & _
" tblRevenue_CIS_Details " & vbCrLf & _
"RIGHT JOIN qryRevenue_CIS_0200_BE " & vbCrLf & _
"ON tblRevenue_CIS_Details.LocID = qryRevenue_CIS_0200_BE.UTLCID; " &
vbCrLf

strSQLstatement2 = _
strSQLinsert2 & vbCrLf & _
strSQLselect2 & vbCrLf & _
strSQLfrom2 & vbCrLf

' MsgBox strSQLstatement2, , "strSQLstatement2 - " & Date & " - " & Time
DAOdbs.QueryDefs("qryRevenue_CIS_0200_FE").SQL = strSQLstatement2

' DoCmd.RunSQL ("CREATE TABLE tblRevenue_CIS_Details")

DAOdbs.Execute "qryRevenue_CIS_0200_FE", dbFailOnError

DAOrs.Close
Set DAOrs = Nothing

DAOdbs.Close
Set DAOdbs = Nothing

DAOdbsBE.Close
Set DAOdbsBE = Nothing

Exit_SQLgetTbl420Trans:

On Error Resume Next
SysCmd acSysCmdClearStatus
Exit Function

Err_SQLgetTbl420Trans:
MsgBox "Error # " & Err.Number & " was generated by " & Err.Source & vbCrLf
& vbCrLf & _
Err.Description, , "RunJob - SQLgetTbl420Trans - " & Date & " - " &
Time
' intSQLgetTbl420Trans = False
Resume Exit_SQLgetTbl420Trans

End Function
'----------------------------------------------------------------------

(Note: I included my Dim statements so you can see that I *do* Dim my
variables.)

When it gets to this statement:

DAOdbs.Execute "qryRevenue_CIS_0200_FE", dbFailOnError

Access opens the 'Select Data Source' window.

As I understand it, and I'm most likely wrong, in light of my problem here, is:

"Set DAOdbs = CurrentDB" = open the current db
"Set DAOdbs = Nothing" = close the 'DAOdbs' db

But I've changed the connection to the second (BE) db (DB2 on AS400). From:

Set DAOdbs = OpenDatabase("", False, False, strConnect)

To:

Set DAOdbsBE = OpenDatabase("", False, False, strConnect)

And Access is *still* prompting me. Also, when my 'Execute' statement was
wrong:

DAOdbsBE.Execute "qryRevenue_CIS_0200_FE", dbFailOnError

Instead of the current:

DAOdbs.Execute "qryRevenue_CIS_0200_FE", dbFailOnError

Access would give me an error msg saying that I couldn't execute a select query.

What is it about opening the DB2 db on the 400 that I appear to be missing? I'd
really like to know. I'd like to understand this correctly, and 'get it right'.

I appreciate your help. Thanks.

Tom

In the first (working) instance, you have:
Set DAOdbs = CurrentDb in 1 Function and then
Set DAOdbs = OpenDatabase("", False, False, strConnect) in another.
Even tho the Function is declared Public, its variables (including object
variables) would be "private" to that Function. So the second "Set" is to a
different object than the 1st so there's no problem

In the second instance, (not working), you have both statements in the same
function. 1st you set the object to the current db, then you try to open a
second database using the same object name.

Either clear the object:

Set DAOdbs = nothing before the second assignment or change 2nd object name.

BTW => You really should properly Declare (Dim) ALL your variables

BAC
Tom said:
I believe this is more of a VBA problem, as my ODBC code works, in
another module...

I have a module with code that I assembled last year. And my ODBC
code works. When I run the code, Access does *not* ask me for a
username & password. When the [working] ODBC code gets executed,
we've started from a macro, which executed the Public function below,
which then called another function in the same module. (The macro
here does only one thing, executes the function below. The same
function which does everything else, such as calling the other
functions, running the queries, whatever, etc.) Here.

Here's some of the code:

Public Function fncRunJobAcctsRecAging()
On Error GoTo Err_RunJobAcctsRecAging

StartTime = Time

DoCmd.Hourglass True
SysCmd acSysCmdSetStatus, ("Running Job...")
DoCmd.SetWarnings False
Set DAOdbs = CurrentDb

intPBOverallMax = 18 ' set MAX value to maximum tasks we'll
have in this module
intPBOverallCntr = 0 ' set overall counter to zero
intPBTaskMax = 18
intPBTaskCntr = 0
DoCmd.OpenForm "frmProgressBar"
Forms!frmProgressBar.Caption = "Utility Billing Accounts
Receivable by Age..."
DoEvents
.
.
' go run the queries

Forms!frmProgressBar.Caption = "Retrieving 'Current' aging data
from Bill History table..."
DoEvents
strAgeGroup = "C"
Call fncSQLgetTbl420Trans
.
.
'--------------------------------------------
Private Function fncSQLgetTbl420Trans()
On Error GoTo Err_SQLgetTbl420Trans

Dim strConnect As String, strSQL As String

strLinkDSNname = "EPPDFIN"
If strLinkDSNname = "" Then
MsgBox "You must supply a DSN in order to link tables."
Else
strConnect = "ODBC;DSN=" & strLinkDSNname & ";uid=" &
strCurrentUser & _
";mode=share;dbalias=" & strLinkDSNname &
";trusted_connection=1;;"
End If
Set DAOdbs = OpenDatabase("", False, False, strConnect)
DoCmd.SetWarnings False
MsgBox strConnect, , "strConnect - " & Date & " - " & Time

SysCmd acSysCmdSetStatus, ("Running Job...")

Set DAOdbs = CurrentDb
Set DAOrs = DAOdbs.OpenRecordset("tblAcctsRecAging_Periods")

' determine the periods for which to retrieve data...
' .
.

In my current module, the one which does *not* work, I've copied the
good code, making only what I *thought* were minor changes. Here my
macro executes the function [below] with the ODBC code, with no call
to another function, as above. (The macro here runs a *lot* of
things, code, queries, etc.) Here's the code:

Public Function fncSQLgetTbl420Trans()
On Error GoTo Err_SQLgetTbl420Trans

Dim strConnect As String, strSQL As String

Set DAOdbs = CurrentDb

strLinkDSNname = "EPPDFIN"
strCurrentUser = Environ$("UserName")

If strLinkDSNname = "" Then
MsgBox "You must supply a DSN in order to link tables."
Else
strConnect = "ODBC;DSN=" & strLinkDSNname & ";uid=" &
strCurrentUser & _
";mode=share;dbalias=" & strLinkDSNname &
";trusted_connection=1;;"
End If

Set DAOdbs = OpenDatabase("", False, False, strConnect)
DoCmd.SetWarnings False
' MsgBox strConnect, , "strConnect - " & Date & " - " & Time

SysCmd acSysCmdSetStatus, ("Running Job...")

DoCmd.RunSQL ("DELETE * FROM tblRevenue_CIS_Details")

I've futsed with it again and again. Whatever *it* is, I don't see
it. And I don't understand why it won't work, seeing as it's
*supposed* to be [essentially] the same code.

If anyone has any thoughts/comments/suggestions I'd appreciate you
sharing it/them with me.

Thanks much in advance,

Tom
Click to expand...
Click to expand...
 
1st thing I notice is that it appears your ' strCurrentUser =
Environ$("UserName")
assignment has been commented out...You should be getting Null or "" for the
UID in your ODBC string..Not kool.. Probably expalins why Access opens prompt
for DSN window..

I'm not familiar with teh SQL structure you've got here, but it looks
"rational". Have you tried using debug.print to get a look at your generated
SQL statements. It's easy to mispalce spaces, etc. that could cause a SQL
bump.

Try that and repost if error continues

BAC




Tom said:
I appreciate your repsonse.

I don't mean to be so...dense...about this, but I'm obviously still missing
something.

I've tried changing my code, and have had only marginal success. Here's what I
have now:

'----------------------------------------------------------------------
Option Compare Database

Public intLoop1 As Variant, intNmbrOfLoops As Variant, intNmbrOfRecs As
Variant
Public intLoop2 As Variant
Public strSQLinsert1 As String, strSQLselect1 As String, strSQLfrom1 As
String
Public strSQLwhere1 As String, strSQLwhere1a As String, strSQLorderby1 As
String
Public strSQLstatement1 As String, strAgeGroup As String
Public intPeriodYear As Variant, intPeriodMonth As Variant,
intPeriodYearPlus1 As Variant
Public strWhichAmtBilled As String, strWhichAmtUnpaid As String
Public strSQLinsert2 As String, strSQLselect2 As String, strSQLfrom2 As
String
Public strSQLon2 As String, strSQLwhere2 As String, strSQLin2 As String
Public strSQLorderby2 As String
Public strSQLstatement2 As String


Public Function fncSQLgetTbl420Trans()
On Error GoTo Err_SQLgetTbl420Trans

Dim strConnect As String, strSQL As String

SysCmd acSysCmdSetStatus, ("Running Job...")

DoCmd.RunSQL ("DELETE * FROM tblRevenue_CIS_Details")

Set DAOdbs = CurrentDb
Set DAOrs = DAOdbs.OpenRecordset("tblRevenue_CIS_Period")

' determine the periods for which to retrieve data...
'
intPeriodYear = DAOrs![PeriodYear]
intPeriodMonth = DAOrs![PeriodMonth]
intPeriodYearPlus1 = DAOrs![PeriodYearPlus1]

' Set DAOdbs = Nothing

strLinkDSNname = "EPPDFIN"
' strCurrentUser = Environ$("UserName")
If strLinkDSNname = "" Then
MsgBox "You must supply a DSN in order to link tables."
Else
strConnect = "ODBC;DSN=" & strLinkDSNname & ";uid=" & strCurrentUser & _
";mode=share;dbalias=" & strLinkDSNname &
";trusted_connection=1;;"
End If
' Set DAOdbs = CurrentDb
Set DAOdbsBE = OpenDatabase("", False, False, strConnect)

DoCmd.SetWarnings False
' MsgBox strConnect, , "strConnect - " & Date & " - " & Time

' compose SQL string 1 for the back end (BE) - DB2
'
strSQLselect1 = _
"SELECT distinct " & vbCrLf & _
" b.UTCSID," & vbCrLf & _
" b.UTLCID," & vbCrLf & _
" b.UTRCLS," & vbCrLf & _
" b.UTSVC," & vbCrLf & _
" b.UTPEYY," & vbCrLf & _
" b.UTPEMM," & vbCrLf & _
" b.UTAGE," & vbCrLf & _
" b.UTTTYP," & vbCrLf & _
" b.UTTDSC," & vbCrLf & _
" b.UTTAMT," & vbCrLf & _
" b.UTUNPD" & vbCrLf

strSQLfrom1 = _
"FROM " & vbCrLf & _
" CXLIB.UT420AP as b " & vbCrLf

strSQLwhere1 = _
"WHERE " & vbCrLf & _
" (((b.UTAGE='C ')) AND (" & _
"(((b.UTPEMM)=" & intPeriodMonth & ") AND ((b.UTPEYY)=" & intPeriodYear
& ")))) " & _
vbCrLf

strSQLorderby1 = _
"ORDER BY " & vbCrLf & _
" b.UTRCLS," & vbCrLf & _
" b.UTSVC," & vbCrLf & _
" b.UTPEYY," & vbCrLf & _
" b.UTPEMM," & vbCrLf & _
" b.UTTTYP," & vbCrLf & _
" b.UTTDSC;" & vbCrLf

strSQLstatement1 = _
strSQLselect1 & vbCrLf & _
strSQLfrom1 & vbCrLf & _
strSQLwhere1 & vbCrLf & _
strSQLorderby1

' MsgBox strSQLstatement1, , "strSQLstatement1 - " & Date & " - " & Time
DAOdbs.QueryDefs("qryRevenue_CIS_0200_BE").SQL = strSQLstatement1

' compose SQL string 2 for the front end (FE) - Access
'
strSQLinsert2 = _
"INSERT INTO tblRevenue_CIS_Details" & vbCrLf & _
"( CustID, " & vbCrLf & _
"LocID, " & vbCrLf & _
"CustClass, " & vbCrLf & _
"Serv, " & vbCrLf & _
"PeriodYear, " & vbCrLf & _
"PeriodMonth, " & vbCrLf & _
"AgeCode, " & vbCrLf & _
"ChgType, " & vbCrLf & _
"ChgDesc, " & vbCrLf & _
"AmtBilled, " & vbCrLf & _
"Unpaid, " & vbCrLf & _
"DateRetrieved ) " & vbCrLf

strSQLselect2 = _
"SELECT " & vbCrLf & _
"qryRevenue_CIS_0200_BE.UTCSID, " & vbCrLf & _
"qryRevenue_CIS_0200_BE.UTLCID, " & vbCrLf & _
"qryRevenue_CIS_0200_BE.UTRCLS, " & vbCrLf & _
"qryRevenue_CIS_0200_BE.UTSVC, " & vbCrLf & _
"qryRevenue_CIS_0200_BE.UTPEYY, " & vbCrLf & _
"qryRevenue_CIS_0200_BE.UTPEMM, " & vbCrLf & _
"qryRevenue_CIS_0200_BE.UTAGE, " & vbCrLf & _
"qryRevenue_CIS_0200_BE.UTTTYP, " & vbCrLf & _
"qryRevenue_CIS_0200_BE.UTTDSC, " & vbCrLf & _
"qryRevenue_CIS_0200_BE.UTTAMT, " & vbCrLf & _
"qryRevenue_CIS_0200_BE.UTUNPD, " & vbCrLf & _
"Now() AS Expr1 " & vbCrLf

strSQLfrom2 = _
"FROM " & vbCrLf & _
" tblRevenue_CIS_Details " & vbCrLf & _
"RIGHT JOIN qryRevenue_CIS_0200_BE " & vbCrLf & _
"ON tblRevenue_CIS_Details.LocID = qryRevenue_CIS_0200_BE.UTLCID; " &
vbCrLf

strSQLstatement2 = _
strSQLinsert2 & vbCrLf & _
strSQLselect2 & vbCrLf & _
strSQLfrom2 & vbCrLf

' MsgBox strSQLstatement2, , "strSQLstatement2 - " & Date & " - " & Time
DAOdbs.QueryDefs("qryRevenue_CIS_0200_FE").SQL = strSQLstatement2

' DoCmd.RunSQL ("CREATE TABLE tblRevenue_CIS_Details")

DAOdbs.Execute "qryRevenue_CIS_0200_FE", dbFailOnError

DAOrs.Close
Set DAOrs = Nothing

DAOdbs.Close
Set DAOdbs = Nothing

DAOdbsBE.Close
Set DAOdbsBE = Nothing

Exit_SQLgetTbl420Trans:

On Error Resume Next
SysCmd acSysCmdClearStatus
Exit Function

Err_SQLgetTbl420Trans:
MsgBox "Error # " & Err.Number & " was generated by " & Err.Source & vbCrLf
& vbCrLf & _
Err.Description, , "RunJob - SQLgetTbl420Trans - " & Date & " - " &
Time
' intSQLgetTbl420Trans = False
Resume Exit_SQLgetTbl420Trans

End Function
'----------------------------------------------------------------------

(Note: I included my Dim statements so you can see that I *do* Dim my
variables.)

When it gets to this statement:

DAOdbs.Execute "qryRevenue_CIS_0200_FE", dbFailOnError

Access opens the 'Select Data Source' window.

As I understand it, and I'm most likely wrong, in light of my problem here, is:

"Set DAOdbs = CurrentDB" = open the current db
"Set DAOdbs = Nothing" = close the 'DAOdbs' db

But I've changed the connection to the second (BE) db (DB2 on AS400). From:

Set DAOdbs = OpenDatabase("", False, False, strConnect)

To:

Set DAOdbsBE = OpenDatabase("", False, False, strConnect)

And Access is *still* prompting me. Also, when my 'Execute' statement was
wrong:

DAOdbsBE.Execute "qryRevenue_CIS_0200_FE", dbFailOnError

Instead of the current:

DAOdbs.Execute "qryRevenue_CIS_0200_FE", dbFailOnError

Access would give me an error msg saying that I couldn't execute a select query.

What is it about opening the DB2 db on the 400 that I appear to be missing? I'd
really like to know. I'd like to understand this correctly, and 'get it right'.

I appreciate your help. Thanks.

Tom

In the first (working) instance, you have:
Set DAOdbs = CurrentDb in 1 Function and then
Set DAOdbs = OpenDatabase("", False, False, strConnect) in another.
Even tho the Function is declared Public, its variables (including object
variables) would be "private" to that Function. So the second "Set" is to a
different object than the 1st so there's no problem

In the second instance, (not working), you have both statements in the same
function. 1st you set the object to the current db, then you try to open a
second database using the same object name.

Either clear the object:

Set DAOdbs = nothing before the second assignment or change 2nd object name.

BTW => You really should properly Declare (Dim) ALL your variables

BAC
Tom said:
I believe this is more of a VBA problem, as my ODBC code works, in
another module...

I have a module with code that I assembled last year. And my ODBC
code works. When I run the code, Access does *not* ask me for a
username & password. When the [working] ODBC code gets executed,
we've started from a macro, which executed the Public function below,
which then called another function in the same module. (The macro
here does only one thing, executes the function below. The same
function which does everything else, such as calling the other
functions, running the queries, whatever, etc.) Here.

Here's some of the code:

Public Function fncRunJobAcctsRecAging()
On Error GoTo Err_RunJobAcctsRecAging

StartTime = Time

DoCmd.Hourglass True
SysCmd acSysCmdSetStatus, ("Running Job...")
DoCmd.SetWarnings False
Set DAOdbs = CurrentDb

intPBOverallMax = 18 ' set MAX value to maximum tasks we'll
have in this module
intPBOverallCntr = 0 ' set overall counter to zero
intPBTaskMax = 18
intPBTaskCntr = 0
DoCmd.OpenForm "frmProgressBar"
Forms!frmProgressBar.Caption = "Utility Billing Accounts
Receivable by Age..."
DoEvents
.
.
' go run the queries

Forms!frmProgressBar.Caption = "Retrieving 'Current' aging data
from Bill History table..."
DoEvents
strAgeGroup = "C"
Call fncSQLgetTbl420Trans
.
.
'--------------------------------------------
Private Function fncSQLgetTbl420Trans()
On Error GoTo Err_SQLgetTbl420Trans

Dim strConnect As String, strSQL As String

strLinkDSNname = "EPPDFIN"
If strLinkDSNname = "" Then
Click to expand...
Click to expand...
Click to expand...
 
1st thing I notice is that it appears your ' strCurrentUser =
Environ$("UserName")
assignment has been commented out...You should be getting Null or "" for the
UID in your ODBC string..Not kool.. Probably expalins why Access opens prompt
for DSN window..
Click to expand...

For 'whatever' reason, not really. I ran the debugger to execute my code in/of
fncSQLgetTbl420Trans(). strCurrentUser did in fact have my username. But for
giggles I uncommented it and tried. No difference.
I'm not familiar with teh SQL structure you've got here, but it looks
"rational". Have you tried using debug.print to get a look at your generated
SQL statements. It's easy to mispalce spaces, etc. that could cause a SQL
bump.
Click to expand...

Not debug.print, usually a msgbox that I comment out when it's no longer needed.
And that's just fine. I've tested the SQL code in both functions. After VBA
builds my queries, I can go to the db window and execute them manually. And the
back end queries I can normally test by executing them manually as well. In
this case doing either, works just fine. My *only* complaint is that I can't
get rid of that dadgum 'Select Data Source' window prompt.
Try that and repost if error continues

BAC
Click to expand...
Is there some *good* book, and/or website that might help me with my problem?

Again, thanks a lot,

Tom
Tom said:
I appreciate your repsonse.

I don't mean to be so...dense...about this, but I'm obviously still missing
something.

I've tried changing my code, and have had only marginal success. Here's what I
have now:

'----------------------------------------------------------------------
Option Compare Database

Public intLoop1 As Variant, intNmbrOfLoops As Variant, intNmbrOfRecs As
Variant
Public intLoop2 As Variant
Public strSQLinsert1 As String, strSQLselect1 As String, strSQLfrom1 As
String
Public strSQLwhere1 As String, strSQLwhere1a As String, strSQLorderby1 As
String
Public strSQLstatement1 As String, strAgeGroup As String
Public intPeriodYear As Variant, intPeriodMonth As Variant,
intPeriodYearPlus1 As Variant
Public strWhichAmtBilled As String, strWhichAmtUnpaid As String
Public strSQLinsert2 As String, strSQLselect2 As String, strSQLfrom2 As
String
Public strSQLon2 As String, strSQLwhere2 As String, strSQLin2 As String
Public strSQLorderby2 As String
Public strSQLstatement2 As String


Public Function fncSQLgetTbl420Trans()
On Error GoTo Err_SQLgetTbl420Trans

Dim strConnect As String, strSQL As String

SysCmd acSysCmdSetStatus, ("Running Job...")

DoCmd.RunSQL ("DELETE * FROM tblRevenue_CIS_Details")

Set DAOdbs = CurrentDb
Set DAOrs = DAOdbs.OpenRecordset("tblRevenue_CIS_Period")

' determine the periods for which to retrieve data...
'
intPeriodYear = DAOrs![PeriodYear]
intPeriodMonth = DAOrs![PeriodMonth]
intPeriodYearPlus1 = DAOrs![PeriodYearPlus1]

' Set DAOdbs = Nothing

strLinkDSNname = "EPPDFIN"
' strCurrentUser = Environ$("UserName")
If strLinkDSNname = "" Then
MsgBox "You must supply a DSN in order to link tables."
Else
strConnect = "ODBC;DSN=" & strLinkDSNname & ";uid=" & strCurrentUser & _
";mode=share;dbalias=" & strLinkDSNname &
";trusted_connection=1;;"
End If
' Set DAOdbs = CurrentDb
Set DAOdbsBE = OpenDatabase("", False, False, strConnect)

DoCmd.SetWarnings False
' MsgBox strConnect, , "strConnect - " & Date & " - " & Time

' compose SQL string 1 for the back end (BE) - DB2
'
strSQLselect1 = _
"SELECT distinct " & vbCrLf & _
" b.UTCSID," & vbCrLf & _
" b.UTLCID," & vbCrLf & _
" b.UTRCLS," & vbCrLf & _
" b.UTSVC," & vbCrLf & _
" b.UTPEYY," & vbCrLf & _
" b.UTPEMM," & vbCrLf & _
" b.UTAGE," & vbCrLf & _
" b.UTTTYP," & vbCrLf & _
" b.UTTDSC," & vbCrLf & _
" b.UTTAMT," & vbCrLf & _
" b.UTUNPD" & vbCrLf

strSQLfrom1 = _
"FROM " & vbCrLf & _
" CXLIB.UT420AP as b " & vbCrLf

strSQLwhere1 = _
"WHERE " & vbCrLf & _
" (((b.UTAGE='C ')) AND (" & _
"(((b.UTPEMM)=" & intPeriodMonth & ") AND ((b.UTPEYY)=" & intPeriodYear
& ")))) " & _
vbCrLf

strSQLorderby1 = _
"ORDER BY " & vbCrLf & _
" b.UTRCLS," & vbCrLf & _
" b.UTSVC," & vbCrLf & _
" b.UTPEYY," & vbCrLf & _
" b.UTPEMM," & vbCrLf & _
" b.UTTTYP," & vbCrLf & _
" b.UTTDSC;" & vbCrLf

strSQLstatement1 = _
strSQLselect1 & vbCrLf & _
strSQLfrom1 & vbCrLf & _
strSQLwhere1 & vbCrLf & _
strSQLorderby1

' MsgBox strSQLstatement1, , "strSQLstatement1 - " & Date & " - " & Time
DAOdbs.QueryDefs("qryRevenue_CIS_0200_BE").SQL = strSQLstatement1

' compose SQL string 2 for the front end (FE) - Access
'
strSQLinsert2 = _
"INSERT INTO tblRevenue_CIS_Details" & vbCrLf & _
"( CustID, " & vbCrLf & _
"LocID, " & vbCrLf & _
"CustClass, " & vbCrLf & _
"Serv, " & vbCrLf & _
"PeriodYear, " & vbCrLf & _
"PeriodMonth, " & vbCrLf & _
"AgeCode, " & vbCrLf & _
"ChgType, " & vbCrLf & _
"ChgDesc, " & vbCrLf & _
"AmtBilled, " & vbCrLf & _
"Unpaid, " & vbCrLf & _
"DateRetrieved ) " & vbCrLf

strSQLselect2 = _
"SELECT " & vbCrLf & _
"qryRevenue_CIS_0200_BE.UTCSID, " & vbCrLf & _
"qryRevenue_CIS_0200_BE.UTLCID, " & vbCrLf & _
"qryRevenue_CIS_0200_BE.UTRCLS, " & vbCrLf & _
"qryRevenue_CIS_0200_BE.UTSVC, " & vbCrLf & _
"qryRevenue_CIS_0200_BE.UTPEYY, " & vbCrLf & _
"qryRevenue_CIS_0200_BE.UTPEMM, " & vbCrLf & _
"qryRevenue_CIS_0200_BE.UTAGE, " & vbCrLf & _
"qryRevenue_CIS_0200_BE.UTTTYP, " & vbCrLf & _
"qryRevenue_CIS_0200_BE.UTTDSC, " & vbCrLf & _
"qryRevenue_CIS_0200_BE.UTTAMT, " & vbCrLf & _
"qryRevenue_CIS_0200_BE.UTUNPD, " & vbCrLf & _
"Now() AS Expr1 " & vbCrLf

strSQLfrom2 = _
"FROM " & vbCrLf & _
" tblRevenue_CIS_Details " & vbCrLf & _
"RIGHT JOIN qryRevenue_CIS_0200_BE " & vbCrLf & _
"ON tblRevenue_CIS_Details.LocID = qryRevenue_CIS_0200_BE.UTLCID; " &
vbCrLf

strSQLstatement2 = _
strSQLinsert2 & vbCrLf & _
strSQLselect2 & vbCrLf & _
strSQLfrom2 & vbCrLf

' MsgBox strSQLstatement2, , "strSQLstatement2 - " & Date & " - " & Time
DAOdbs.QueryDefs("qryRevenue_CIS_0200_FE").SQL = strSQLstatement2

' DoCmd.RunSQL ("CREATE TABLE tblRevenue_CIS_Details")

DAOdbs.Execute "qryRevenue_CIS_0200_FE", dbFailOnError

DAOrs.Close
Set DAOrs = Nothing

DAOdbs.Close
Set DAOdbs = Nothing

DAOdbsBE.Close
Set DAOdbsBE = Nothing

Exit_SQLgetTbl420Trans:

On Error Resume Next
SysCmd acSysCmdClearStatus
Exit Function

Err_SQLgetTbl420Trans:
MsgBox "Error # " & Err.Number & " was generated by " & Err.Source & vbCrLf
& vbCrLf & _
Err.Description, , "RunJob - SQLgetTbl420Trans - " & Date & " - " &
Time
' intSQLgetTbl420Trans = False
Resume Exit_SQLgetTbl420Trans

End Function
'----------------------------------------------------------------------

(Note: I included my Dim statements so you can see that I *do* Dim my
variables.)

When it gets to this statement:

DAOdbs.Execute "qryRevenue_CIS_0200_FE", dbFailOnError

Access opens the 'Select Data Source' window.

As I understand it, and I'm most likely wrong, in light of my problem here, is:

"Set DAOdbs = CurrentDB" = open the current db
"Set DAOdbs = Nothing" = close the 'DAOdbs' db

But I've changed the connection to the second (BE) db (DB2 on AS400). From:

Set DAOdbs = OpenDatabase("", False, False, strConnect)

To:

Set DAOdbsBE = OpenDatabase("", False, False, strConnect)

And Access is *still* prompting me. Also, when my 'Execute' statement was
wrong:

DAOdbsBE.Execute "qryRevenue_CIS_0200_FE", dbFailOnError

Instead of the current:

DAOdbs.Execute "qryRevenue_CIS_0200_FE", dbFailOnError

Access would give me an error msg saying that I couldn't execute a select query.

What is it about opening the DB2 db on the 400 that I appear to be missing? I'd
really like to know. I'd like to understand this correctly, and 'get it right'.

I appreciate your help. Thanks.

Tom

In the first (working) instance, you have:
Set DAOdbs = CurrentDb in 1 Function and then
Set DAOdbs = OpenDatabase("", False, False, strConnect) in another.
Even tho the Function is declared Public, its variables (including object
variables) would be "private" to that Function. So the second "Set" is to a
different object than the 1st so there's no problem

In the second instance, (not working), you have both statements in the same
function. 1st you set the object to the current db, then you try to open a
second database using the same object name.

Either clear the object:

Set DAOdbs = nothing before the second assignment or change 2nd object name.

BTW => You really should properly Declare (Dim) ALL your variables

BAC
:

I believe this is more of a VBA problem, as my ODBC code works, in
another module...

I have a module with code that I assembled last year. And my ODBC
code works. When I run the code, Access does *not* ask me for a
username & password. When the [working] ODBC code gets executed,
we've started from a macro, which executed the Public function below,
which then called another function in the same module. (The macro
here does only one thing, executes the function below. The same
function which does everything else, such as calling the other
functions, running the queries, whatever, etc.) Here.

Here's some of the code:

Public Function fncRunJobAcctsRecAging()
On Error GoTo Err_RunJobAcctsRecAging

StartTime = Time

DoCmd.Hourglass True
SysCmd acSysCmdSetStatus, ("Running Job...")
DoCmd.SetWarnings False
Set DAOdbs = CurrentDb

intPBOverallMax = 18 ' set MAX value to maximum tasks we'll
have in this module
intPBOverallCntr = 0 ' set overall counter to zero
intPBTaskMax = 18
intPBTaskCntr = 0
DoCmd.OpenForm "frmProgressBar"
Forms!frmProgressBar.Caption = "Utility Billing Accounts
Receivable by Age..."
DoEvents
.
.
' go run the queries

Forms!frmProgressBar.Caption = "Retrieving 'Current' aging data
from Bill History table..."
DoEvents
strAgeGroup = "C"
Call fncSQLgetTbl420Trans
.
.
'--------------------------------------------
Private Function fncSQLgetTbl420Trans()
On Error GoTo Err_SQLgetTbl420Trans

Dim strConnect As String, strSQL As String

strLinkDSNname = "EPPDFIN"
If strLinkDSNname = "" Then
Click to expand...
Click to expand...
Click to expand...
 
Back
Top