A VB program interface with Access - Question (longish - code included)

Hello,

If this is not the place to post this please point me at the correct one.

I am neither an SQL, Access, nor VB6 specialist nor expert, but I
have been given a VB6 program to modify slightly, recompile (using
VB6) and then install. (I considered upgrading to VS2008 but got
more compilation error messages than I knew what to do with, so I
had to rule that out as I'm running against a tight deadline.)

I've made the necessary changes. The recompilation works fine. But
when I execute the program the program I get the following error
message:
Run-time error '-2147217887 (80040e21)':
Multiple-step OLE DB operation generated errors.
Check each OLE DB status value, if available.
No work was done.

I recovered another copy of the original code, made no changes, and
recompiled the program again with exactly the same results. (There
was no such error in the compiled version from 3 years ago.) I've
tried inserting (better) error processing in the program (as it was
and also in the altered version). In all cases the problem remains.

I have narrowed it down - I think - to a certain subset of code but
have not been to identify a method of resolving the problem. Since
the entire interface is within a single VB Sub(routine) I've added
it to the end of this message.

I might note that I tried to increase the connection time with no
visible result.

Any suggestions on how I can:

= get it to compile "as is" without having the program abort
when it tries to insert the code into the Access database; or,
= how to divide the indicated lines into two segments which can
be executed as (if it were) a single act of inserting the data
into the database; or,
= some other suggestion I haven't been able to come up with.

I'd appreciate any assistance/suggestions that might be provided.

David

Here's the code:

Private Sub WriteEntry(q_strThisLab)
'this function writes the entry to the database
Dim adoConnection As ADODB.Connection
Dim rsRecordset As ADODB.Recordset
Dim strSql As String
Dim z_strLab As String
z_strLab = q_strThisLab
If z_strLab "" Then
' strSql = "Select * from Lab_Entries"
Select Case Left(z_strLab, 1)
Case "C"
strSql = "Select * from dta_EntriesMCH"
Case "S"
strSql = "Select * from dta_EntriesRMS"
Case "U"
strSql = "Select * from dta_EntriesUUB"
Case "M"
strSql = "Select * from dta_EntriesME"
Case Else
End Select
Else
' MsgBox ("Lab name is " & z_strLab)
End If

If p_blnManualEntry = True Then
p_strLabMonitor = "MONITOR:" & p_strLabMonitor
Else
'!Raw_Data = p_strRawData
End If

Set adoConnection = New ADODB.Connection
adoConnection.ConnectionTimeout = 25
adoConnection.Open (p_strConnectionString)
Set rsRecordset = New ADODB.Recordset
rsRecordset.Open strSql, adoConnection, adOpenKeyset, _
adLockPessimistic

' I've been able to narrow the error down to the somewhere
' in the "with block." As near as I can tell, it occurs at
' or about the seventh line (!Time_Data = ...).

With rsRecordset
.AddNew
!Lab = p_strThisLab
!ISO_Number = p_strISONum
!First_Name = p_strFirstName
!Last_Name = p_strLastName
!Allowed_Entry = p_blnEntryAllowed
!Raw_Data = p_strRawData
!Time_Date = p_dtReadTime
!Read_Error = p_blnReadError
!Manual_Entry = p_blnManualEntry
.Update
End With

' End of the apparent error. (I might note that some
' of the possible reported causes were that the data
' type of the line didn't match. I checked the data-
' base and each of the type of each of these fields
' matches the type within the Access database.

Set reRecordset = Nothing
Set adoConnection = Nothing

End Sub

Hi David,

One quick basic question: Have you run in in debug mode and stepped
through that section to see exactly which line causes the problem? If not,
try it. Then once you know the line run it again and inspect the value that
is causing the error. That may give you a clue.

One change that you could try that might make a difference: change
adOpenKeyset to adOpenDynamic and adLockPessimistic to adLockOptimistic. I
have seen where certain combinations of those particular settings work better
than others. In answer to your question about splitting it up; it is doable,
but it would complicate the matter more than it would be worth.

Hope this helps,

Clifford Bass

"David Schrader" wrote:

Hello,

If this is not the place to post this please point me at the correct one.

I am neither an SQL, Access, nor VB6 specialist nor expert, but I
have been given a VB6 program to modify slightly, recompile (using
VB6) and then install. (I considered upgrading to VS2008 but got
more compilation error messages than I knew what to do with, so I
had to rule that out as I'm running against a tight deadline.)

I've made the necessary changes. The recompilation works fine. But
when I execute the program the program I get the following error
message:
Run-time error '-2147217887 (80040e21)':
Multiple-step OLE DB operation generated errors.
Check each OLE DB status value, if available.
No work was done.

I recovered another copy of the original code, made no changes, and
recompiled the program again with exactly the same results. (There
was no such error in the compiled version from 3 years ago.) I've
tried inserting (better) error processing in the program (as it was
and also in the altered version). In all cases the problem remains.

I have narrowed it down - I think - to a certain subset of code but
have not been to identify a method of resolving the problem. Since
the entire interface is within a single VB Sub(routine) I've added
it to the end of this message.

I might note that I tried to increase the connection time with no
visible result.

Any suggestions on how I can:

= get it to compile "as is" without having the program abort
when it tries to insert the code into the Access database; or,
= how to divide the indicated lines into two segments which can
be executed as (if it were) a single act of inserting the data
into the database; or,
= some other suggestion I haven't been able to come up with.

I'd appreciate any assistance/suggestions that might be provided.

David

Here's the code:

Private Sub WriteEntry(q_strThisLab)
'this function writes the entry to the database
Dim adoConnection As ADODB.Connection
Dim rsRecordset As ADODB.Recordset
Dim strSql As String
Dim z_strLab As String
z_strLab = q_strThisLab
If z_strLab "" Then
' strSql = "Select * from Lab_Entries"
Select Case Left(z_strLab, 1)
Case "C"
strSql = "Select * from dta_EntriesMCH"
Case "S"
strSql = "Select * from dta_EntriesRMS"
Case "U"
strSql = "Select * from dta_EntriesUUB"
Case "M"
strSql = "Select * from dta_EntriesME"
Case Else
End Select
Else
' MsgBox ("Lab name is " & z_strLab)
End If

If p_blnManualEntry = True Then
p_strLabMonitor = "MONITOR:" & p_strLabMonitor
Else
'!Raw_Data = p_strRawData
End If

Set adoConnection = New ADODB.Connection
adoConnection.ConnectionTimeout = 25
adoConnection.Open (p_strConnectionString)
Set rsRecordset = New ADODB.Recordset
rsRecordset.Open strSql, adoConnection, adOpenKeyset, _
adLockPessimistic

' I've been able to narrow the error down to the somewhere
' in the "with block." As near as I can tell, it occurs at
' or about the seventh line (!Time_Data = ...).

With rsRecordset
.AddNew
!Lab = p_strThisLab
!ISO_Number = p_strISONum
!First_Name = p_strFirstName
!Last_Name = p_strLastName
!Allowed_Entry = p_blnEntryAllowed
!Raw_Data = p_strRawData
!Time_Date = p_dtReadTime
!Read_Error = p_blnReadError
!Manual_Entry = p_blnManualEntry
.Update
End With

' End of the apparent error. (I might note that some
' of the possible reported causes were that the data
' type of the line didn't match. I checked the data-
' base and each of the type of each of these fields
' matches the type within the Access database.

Set reRecordset = Nothing
Set adoConnection = Nothing

End Sub

Clifford,

Yes, I did run it in debug mode. So nothing there.
Tried your suggestions about "adOpenKeyset" and
"adLockPessimistic" and the problem was solved
and now I'm getting a new (set of) error(s).

"Run-time error 80004005"
"[Microsoft][ODBC Driver Manager] Data source name not found
and no default driver specified.

The program reads the connection string from the
registry:
{Microsoft Access Driver (*.mdb)}; DBQ=C:\[Swipe]\SwipeDB\MainDB.mdb
I checked the values read and they are being read
in properly and they are still the same just before I
open the database so I'm a little lost on what's going
on. I'm learning lot more about Access than I had
in mind when I started ("It's just a couple of simple
changes in where files are stored so it shouldn't
take more that a day to beat it out.")

Thanks for your help.

David





"Clifford Bass" wrote in message
...
Hi David,

One quick basic question: Have you run in in debug mode and stepped
through that section to see exactly which line causes the problem? If
not,
try it. Then once you know the line run it again and inspect the value
that
is causing the error. That may give you a clue.

One change that you could try that might make a difference: change
adOpenKeyset to adOpenDynamic and adLockPessimistic to adLockOptimistic.
I
have seen where certain combinations of those particular settings work
better
than others. In answer to your question about splitting it up; it is
doable,
but it would complicate the matter more than it would be worth.

Hope this helps,

Clifford Bass

"David Schrader" wrote:

Hello,

If this is not the place to post this please point me at the correct one.

I am neither an SQL, Access, nor VB6 specialist nor expert, but I
have been given a VB6 program to modify slightly, recompile (using
VB6) and then install. (I considered upgrading to VS2008 but got
more compilation error messages than I knew what to do with, so I
had to rule that out as I'm running against a tight deadline.)

I've made the necessary changes. The recompilation works fine. But
when I execute the program the program I get the following error
message:
Run-time error '-2147217887 (80040e21)':
Multiple-step OLE DB operation generated errors.
Check each OLE DB status value, if available.
No work was done.

I recovered another copy of the original code, made no changes, and
recompiled the program again with exactly the same results. (There
was no such error in the compiled version from 3 years ago.) I've
tried inserting (better) error processing in the program (as it was
and also in the altered version). In all cases the problem remains.

I have narrowed it down - I think - to a certain subset of code but
have not been to identify a method of resolving the problem. Since
the entire interface is within a single VB Sub(routine) I've added
it to the end of this message.

I might note that I tried to increase the connection time with no
visible result.

Any suggestions on how I can:

= get it to compile "as is" without having the program abort
when it tries to insert the code into the Access database; or,
= how to divide the indicated lines into two segments which can
be executed as (if it were) a single act of inserting the data
into the database; or,
= some other suggestion I haven't been able to come up with.

I'd appreciate any assistance/suggestions that might be provided.

David

Here's the code:

Private Sub WriteEntry(q_strThisLab)
'this function writes the entry to the database
Dim adoConnection As ADODB.Connection
Dim rsRecordset As ADODB.Recordset
Dim strSql As String
Dim z_strLab As String
z_strLab = q_strThisLab
If z_strLab "" Then
' strSql = "Select * from Lab_Entries"
Select Case Left(z_strLab, 1)
Case "C"
strSql = "Select * from dta_EntriesMCH"
Case "S"
strSql = "Select * from dta_EntriesRMS"
Case "U"
strSql = "Select * from dta_EntriesUUB"
Case "M"
strSql = "Select * from dta_EntriesME"
Case Else
End Select
Else
' MsgBox ("Lab name is " & z_strLab)
End If

If p_blnManualEntry = True Then
p_strLabMonitor = "MONITOR:" & p_strLabMonitor
Else
'!Raw_Data = p_strRawData
End If

Set adoConnection = New ADODB.Connection
adoConnection.ConnectionTimeout = 25
adoConnection.Open (p_strConnectionString)
Set rsRecordset = New ADODB.Recordset
rsRecordset.Open strSql, adoConnection, adOpenKeyset, _
adLockPessimistic

' I've been able to narrow the error down to the somewhere
' in the "with block." As near as I can tell, it occurs at
' or about the seventh line (!Time_Data = ...).

With rsRecordset
.AddNew
!Lab = p_strThisLab
!ISO_Number = p_strISONum
!First_Name = p_strFirstName
!Last_Name = p_strLastName
!Allowed_Entry = p_blnEntryAllowed
!Raw_Data = p_strRawData
!Time_Date = p_dtReadTime
!Read_Error = p_blnReadError
!Manual_Entry = p_blnManualEntry
.Update
End With

' End of the apparent error. (I might note that some
' of the possible reported causes were that the data
' type of the line didn't match. I checked the data-
' base and each of the type of each of these fields
' matches the type within the Access database.

Set reRecordset = Nothing
Set adoConnection = Nothing

End Sub

Hi David,

I would suggest using the OLEDB Jet Engine driver instead of ODBC. Try
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\[Swipe]\SwipeDB\MainDB.mdb"
for your connection string. It probably will be faster; I think the ODBC
driver may well use the OLEDB driver. And I do not think it will impact
your various SQL statements. I'm curious... does your initial directory
really have square brackets in its name?

Clifford Bass

"David Schrader" wrote:

Clifford,

Yes, I did run it in debug mode. So nothing there.
Tried your suggestions about "adOpenKeyset" and
"adLockPessimistic" and the problem was solved
and now I'm getting a new (set of) error(s).

"Run-time error 80004005"
"[Microsoft][ODBC Driver Manager] Data source name not found
and no default driver specified.

The program reads the connection string from the
registry:
{Microsoft Access Driver (*.mdb)}; DBQ=C:\[Swipe]\SwipeDB\MainDB.mdb
I checked the values read and they are being read
in properly and they are still the same just before I
open the database so I'm a little lost on what's going
on. I'm learning lot more about Access than I had
in mind when I started ("It's just a couple of simple
changes in where files are stored so it shouldn't
take more that a day to beat it out.")

Thanks for your help.

David

Clifford,

Will do.

Just an a tangential issue - can you think of any reason why
the code would compile and run *without error* on one
machine but would issue the "Run-time error 80004005"
message on another machine (same OS [XPPro/SP3& up
to date on all service packs], same hardware except one
has USB mouse and kbd and the other doesn't) One with
VS2008 and 1 without.

I'm thinking VS2008 has installed something that facilitates
it working but haven't the faintest idea where to begin to
try to find out what the specific differences might be.

(And there are many many software differences as well.
The VS2008 is my development machine the other is a
mock-up of what the final host* machine will be like.)

Let you know how the Jet stuff works.

David

"Clifford Bass" wrote in message
...
Hi David,

I would suggest using the OLEDB Jet Engine driver instead of ODBC.
Try
"Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\[Swipe]\SwipeDB\MainDB.mdb"
for your connection string. It probably will be faster; I think the ODBC
driver may well use the OLEDB driver. And I do not think it will impact
your various SQL statements. I'm curious... does your initial directory
really have square brackets in its name?

Clifford Bass

"David Schrader" wrote:

Clifford,

Yes, I did run it in debug mode. So nothing there.
Tried your suggestions about "adOpenKeyset" and
"adLockPessimistic" and the problem was solved
and now I'm getting a new (set of) error(s).

"Run-time error 80004005"
"[Microsoft][ODBC Driver Manager] Data source name not found
and no default driver specified.

The program reads the connection string from the
registry:
{Microsoft Access Driver (*.mdb)}; DBQ=C:\[Swipe]\SwipeDB\MainDB.mdb
I checked the values read and they are being read
in properly and they are still the same just before I
open the database so I'm a little lost on what's going
on. I'm learning lot more about Access than I had
in mind when I started ("It's just a couple of simple
changes in where files are stored so it shouldn't
take more that a day to beat it out.")

Thanks for your help.

David

Hi David,

Your instincts on that are probably right-on about needed, but missing
files. Have you created an installation project for the application. Or do
you just copy the EXE over? If just copying, I would suggest shifting to an
installation program. It can be used to make sure the target machine has the
appropriate/required packages (i.e. .NET Framework 3.5) and files. The
installation project wizard may well get what you need with very little
trouble. And the appropriate discussion group would be a good resource for
help if needed.

Clifford Bass

"David Schrader" wrote:

Clifford,

Will do.

Just an a tangential issue - can you think of any reason why
the code would compile and run *without error* on one
machine but would issue the "Run-time error 80004005"
message on another machine (same OS [XPPro/SP3& up
to date on all service packs], same hardware except one
has USB mouse and kbd and the other doesn't) One with
VS2008 and 1 without.

I'm thinking VS2008 has installed something that facilitates
it working but haven't the faintest idea where to begin to
try to find out what the specific differences might be.

(And there are many many software differences as well.
The VS2008 is my development machine the other is a
mock-up of what the final host* machine will be like.)

Let you know how the Jet stuff works.

David

Clifford,

So far just copying the .exe over. Guess I'm going to
have to invest the time on learning how to build the
installation set. (Haven't had a chance to try your
last suggestion yet. Will still let you know.)

(What is the "appropriate" discussion group?" Just
e-mail it to me, no point in blanketing the world.)

David

"Clifford Bass" wrote in message
...
Hi David,

Your instincts on that are probably right-on about needed, but missing
files. Have you created an installation project for the application. Or
do
you just copy the EXE over? If just copying, I would suggest shifting to
an
installation program. It can be used to make sure the target machine has
the
appropriate/required packages (i.e. .NET Framework 3.5) and files. The
installation project wizard may well get what you need with very little
trouble. And the appropriate discussion group would be a good resource
for
help if needed.

Clifford Bass

"David Schrader" wrote:

Clifford,

Will do.

Just an a tangential issue - can you think of any reason why
the code would compile and run *without error* on one
machine but would issue the "Run-time error 80004005"
message on another machine (same OS [XPPro/SP3& up
to date on all service packs], same hardware except one
has USB mouse and kbd and the other doesn't) One with
VS2008 and 1 without.

I'm thinking VS2008 has installed something that facilitates
it working but haven't the faintest idea where to begin to
try to find out what the specific differences might be.

(And there are many many software differences as well.
The VS2008 is my development machine the other is a
mock-up of what the final host* machine will be like.)

Let you know how the Jet stuff works.

David