Solved

Vb 6.0 hangs when doing an append query

Posted on 2002-05-29
11
487 Views
Last Modified: 2007-12-19
My Vb 6.0 program hangs on an NT 4.0 server when trying to append a large amount of data to another table using an ACCESS 97 database.  This same append query works when called in the ACCESS database itself so there is enough memory to perform this.  My code is written below:

Set wrkODBC = CreateWorkspace("", "admin", "", dbUseODBC)
  Set conRao = wrkODBC.OpenConnection("", , , _
        "ODBC;DATABASE=RAOXref;UID=;PWD=;DSN=RAOXref")'Cre

'Create the new table EQXR_XREF_2

  InsertSQLstring = "INSERT INTO EQXR_XREF_2 SELECT EQXR_XREF.* FROM EQXR_XREF"

  conRao.Execute InsertSQLstring
 
 NOTE: It hangs at this point. Is there another method to try or is there a VB 6.0 bug that I am not aware of?


0
Comment
Question by:FidelMike
  • 4
  • 3
  • 2
  • +1
11 Comments
 
LVL 2

Expert Comment

by:Crin
ID: 7042928
Hi,

Use
Select into
construction instead.

Sincerely,
Crin
0
 
LVL 2

Expert Comment

by:Crin
ID: 7042931
IE,

Select * into EQXR_XREF_2 FROM EQXR_XREF;

Sincerely,
Crin

0
 

Author Comment

by:FidelMike
ID: 7042988
Unfortunately, neither of these work as I am receiving an ODBC error #3146
0
 
LVL 2

Expert Comment

by:Crin
ID: 7043238
Try to reinstall latest MDAC, take it here:
http://www.microsoft.com/data/download_270RTM.htm

As a test try to use DAO instead of ADO.

Sincerely,
Crin
0
 

Accepted Solution

by:
beachbum714 earned 50 total points
ID: 7043476
Have you tried using ADO?  How many records and columns are in the table?  Try this, which is based on the Jet 4.0 OLEDB Provider.  It worked on my Access 2000 mdb.  Let me know if you have any questions.

Public Sub Main()
Dim cd As ADODB.Command

   Set cd as New ADODB.Command
   cd.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=<the full path to your mdb file>"
   cd.CommandType = adCmdText
   cd.CommandText = "INSERT INTO EQXR_XREF_2 SELECT EQXR_XREF.* FROM EQXR_XREF"
   cd.Execute
   Set cd = nothing

End Main
   

0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 1

Expert Comment

by:Moondancer
ID: 7078679
Greetings.

This question has been locked with a Proposed Answer, and remains open today.

If the Proposed Answer did not serve your needs, please reject it and comment with an update.  If the Proposed Answer helped you, please accept it to grade and close this question.  If you need help splitting points between multiple experts, please comment here with details so we can help you.

EXPERTS ->  Please guide me here in terms of closing recommendations if the Asker does not respond in 4 days.

Thanks to all,
Moondancer - EE Moderator
0
 
LVL 2

Expert Comment

by:Crin
ID: 7082737
Hello,

Proposed answer should be considered as a comment, it does not correspond to the needs of asker, also it contain mistake (End Main instead of End Sub). It seems like FidelMike had a trouble with MDAC which is quite a common problem (on my statistics about 2% of installations sooner or later get such problem).

Sincerely,
Crin
0
 

Expert Comment

by:beachbum714
ID: 7084255
You are receiving an ODBC error for SQL Server 4.6-6.5.  Which version of ODBC are you using?  Are you still using portions of the code listed above?  Also, have you upgraded your MDAC to 2.7?  There is an update that is necessary for the Jet 4.0 OLEDB provider that will need to be run too.

I'd scrap the DAO code and work with ADO.  Access 97/2000 work with the Jet 4.0 OLEDB provider which has the most capabilities of any OLEDB provider.

Below is the knowledge base article for this error.  Below it is a knowledge base article for trapping the error.  It will give you better information as to the cause of the error.  Below that article is "DAO Transactions to ODBC Database Can Hang Application."

PRB: ODBC Call Failed or Error 3146

Q160762


--------------------------------------------------------------------------------
The information in this article applies to:

Microsoft SQL Server versions 4.2x, 6.0, 6.5
Microsoft Open Database Connectivity, version 1.x, 2.5, 3.0

--------------------------------------------------------------------------------


SYMPTOMS
If ODBC 2.x drivers pass function calls to the ODBC 1.x Driver Manager, you may receive the following errors from any 16-bit application that uses ODBC on a Windows 16-bit environment (such as Windows for Workgroups 3.11 or Windows 3.1).

ODBC Call Failed
-or-
3146 error - driver doesn't support the function



CAUSE
Early versions of the ODBC Driver Manager (1.x) were placed in the Windows directory on 16-bit Windows platforms, while subsequent versions of the ODBC Driver Manager (2.x and above) were placed in the Windows\System directory. The best way to determine if you are encountering the problem is to search the computer for the Odbc.dll file, and verify the version in File Manager. You can do this by clicking on the file and then clicking Properties on the File menu. If the first possible Odbc.dll file in the path is the ODBC 1.x Driver Manager, you are most likely encountering the problem.



WORKAROUND
To work around this problem, remove the ODBC 1.x Driver Manager (Odbc.dll) from the path and/or place the ODBC 2.x Driver Manager (Odbc.dll) in the path first.



MORE INFORMATION
The following portion of an ODBC Spy trace was obtained from an instance in which the error reported was "ODBC Call Failed" from Access (the SQLSetParam was replaced in ODBC 2.0 with SQLBindParameter):



   SQLSetParam
      0x01010009
      1
      SQL_C_DEFAULT
      SQL_VARCHAR
      11
      0
      0x6E16774D
      0x6A16774D
      SQL_ERROR
   SQLError
      NULL
      NULL
      0x01010009
      [5]IM001
      -8190
      [54][SYWARE][ODBCSPY]Driver does not support this function
      511
      54
      SQL_SUCCESS
   SQLError
      NULL
      NULL
      0x01010009
      [5]00000
      -8190
      [0]
      511
      0
      SQL_NO_DATA_FOUND.

Additional query words: 1.xx 2.50

Keywords : kbenv kbinterop kbsetup kbusage kbprb
Issue type :
Technology : kbSQLServSearch kbAudDeveloper kbSQLServ600 kbSQLServ650 kbODBCSearch kbSQLServ420



HOWTO: Get More Information on the ODBC Call Failed Error

Q161288


--------------------------------------------------------------------------------
The information in this article applies to:

Microsoft Visual Basic Professional Edition for Windows, versions 5.0, 6.0
Microsoft Visual Basic Enterprise Edition for Windows, versions 5.0, 6.0
Microsoft Data Access Components version 2.5

--------------------------------------------------------------------------------


SUMMARY
This article describes how to get more information on the ODBC Call Failed error. When errors occur using ODBC databases, Visual Basic will provide an "ODBC Call Failed" error message. This generic error message provides no specific detail so you must cycle through the Errors collection to get additional information. Below is a code sample that shows the difference in behavior.



MORE INFORMATION
The DBEngine has an Errors collection that can be manipulated by the FOR- EACH construct. The JET Engine can store multiple errors in the DBEngine Errors collection. In Visual Basic 3.0, it was possible to parse the string using the routine shown on Page 175 of the Visual Basic 4.0 Professional Features Book under the "Guide to Data Access Objects" section. The # symbol was used to separate the "ODBC Call Failed" message from the detailed ODBC description in Visual Basic 3.0. However, this is not necessary under Visual Basic versions 4.0 and 5.0.

For the example below, a two-field table called MyTable has been set up on an ODBC Source and a primary key set on the ID Field. Two records have been added as below:



   Field      ID      Description
   ===============================
   Record 1    1      Hello
   Record 2    2      World

The code below will generate an error by trying to add a record with a duplicate primary key value to test the code:

Start a new Standard EXE project. Form1 is added by default.


Add a CommandButton to Form1.


Add the following code to the General Declarations section of Form1:



      Option Explicit

      Private Sub Command1_Click()
        Dim db As Database
        Dim rs As Recordset
        On Error GoTo trap
        Set db = OpenDatabase("")
        Set rs = db.OpenRecordset("Select * from MyTable")
        rs.AddNew
          rs.Fields(0).Value = 2
        rs.Update
        Exit Sub
      trap:
        MsgBox Errors.Count
        MsgBox Err.Number & " " & Err.Description
      End Sub
Press the F5 key to run the project. Click on the CommandButton and you should receive error 3146, "ODBC Call Failed." Although the Error count is greater than one, only one message will be displayed.


Remove the code from within the error trap and replace it with one of the following error handlers:



      ' DAO Error Handler
      Dim MyError As Error
      MsgBox Errors.Count
      For Each MyError In DBEngine.Errors
        With MyError
          MsgBox .Number & " " & .Description
        End With
      Next MyError

      ' RDO Error Handler
      Dim MyError As rdoError
      MsgBox rdoErrors.Count
      For Each MyError In rdoEngine.rdoErrors
        With MyError
          MsgBox .Number & " " & .Description
        End With
      Next MyError
 
Press the F5 key to run the project. You should see a detailed message and then the 3146 Error for "ODBC Call Failed."





REFERENCES
Visual Basic 4.0 Professional Features, Chapter 9 of the "Guide to Data Access Object"

Jet Database Engine Programmers Guide, pages 425-427

For additional information, please see the following article in the Microsoft Knowledge Base:


Q120763 How to Retrieve Info from RAISERROR Function in SQL Server DB

Additional query words: errors Collection

Keywords : kbODBC kbRDO kbVBp kbVBp500 kbVBp600 kbGrpDSVBDB kbDSupport kbMDAC250
Issue type : kbhowto
Technology : kbVBSearch kbAudDeveloper kbZNotKeyword6 kbZNotKeyword2 kbVB500Search kbVB600Search kbVBA500 kbVBA600 kbVB500 kbVB600 kbMDACSearch kbMDAC250



PRB: DAO Transactions to ODBC Database Can Hang Application

Q170548


--------------------------------------------------------------------------------
The information in this article applies to:

Microsoft Visual Basic Professional Edition for Windows, versions 5.0, 6.0
Microsoft Visual Basic Enterprise Edition for Windows, versions 5.0, 6.0
Microsoft Visual Basic Professional Edition, 16-bit, for Windows, version 4.0
Microsoft Visual Basic Professional Edition, 32-bit, for Windows, version 4.0
Microsoft Visual Basic Enterprise Edition, 16-bit, for Windows, version 4.0
Microsoft Visual Basic Enterprise Edition, 32-bit, for Windows, version 4.0

--------------------------------------------------------------------------------


SYMPTOMS
When using transactions to an ODBC database, it is possible to cause a Visual Basic application to appear to hang until the ODBC query time-out interval is reached and error 3146 "ODBC Call Failed" is raised. The DBEngine errors collection will show an ODBC "Time-out Expired" error.

The Visual Basic application appears to hang and will eventually display and error with an ODBC Time-out message after the interval specified in the QueryTimeout property of the database object (default = 60 seconds). If the error is trapped and handled, the application can resume normally although the data may have been rolled back or partially committed.



CAUSE
When the Microsoft Jet Engine, used by Visual Basic, performs transactions on an ODBC database, it issues ODBC transactions down each connection that it opens between the BeginTrans and the CommitTrans/Rollback. It then commits or rolls back all of the transactions on all of the connections when a CommitTrans or Rollback is issued.

It must do this because the Jet engine offers global and nested transactions while the current ODBC specification does not; it only allows for a single transaction on a per connection basis.

If needed, the Jet engine will open multiple connections in order to perform the transactions. The server sees each connection as a separate process. It has no way of knowing that it is from the same application or that you may want the connections to get along with each other in terms of locking or available data. It simply treats each connection as a completely independent and distinct entity.

This raises a problem. If the Jet engine opens a connection, begins a transaction that affects a given page of data on a given table, then opens another connection and begins another transaction that affects the same page of data, the server will block (serialize) the second connection until the first one releases the locks (completes). This causes the Visual Basic application to hang on the line of code that is running the statement on the second connection until the transaction that it sent on the first connection completes. This can never happen because the transaction on the first connection is open until a CommitTrans or Rollback is encountered in the Visual Basic code, but that code is hung waiting for the transaction to complete. It is hung indefinitely unless a time-out occurs on one of the connections, which is what eventually happens.

The following example demonstrates the problem with most ODBC servers:

Create a datasource for your server named DSN_BLK_TEST using the ODBC Administrator applet.


Open Visual Basic and start a new project.


Reference the appropriate DAO reference in the References dialog.


Add a CommandButton to the form.


Add the following code to the click event of the CommandButton:

      Private Sub Command1_Click()

         Dim db As Database
         Dim rs As Recordset
         Dim strSQL As String
         Dim intLoop As Integer

         'Open up the ODBC datasource
         Set db = DBEngine.Workspaces(0).OpenDatabase("", _
         False, False, "ODBC;DSN=DSN_BLK_TEST;DATABASE=TestDB;UID=sa;PWD=;")
         'Create a table with two fields one a primary key
         strSQL = "IF EXISTS(SELECT * FROM sysobjects WHERE id = "
         strSQL = strSQL & "object_id('t_blocking')) DROP TABLE t_blocking"
         db.Execute strSQL, dbSQLPassThrough
         strSQL = "CREATE TABLE t_blocking (f_pkey int " & _
                  "NOT NULL PRIMARY KEY,"
         strSQL = strSQL & " f_dummy varchar(10) NULL)"
         db.Execute strSQL, dbSQLPassThrough
         Set rs = db.OpenRecordset("SELECT * FROM t_blocking")
         'Demonstrate Blocking
         BeginTrans
         'Add some records
         For intLoop = 1 To 25
            rs.AddNew
            rs.Fields("f_pkey") = intLoop
            rs.Fields("f_dummy") = "test" & Str$(intLoop)
            rs.Update
         Next intLoop
         'Try to update a few records. This should cause a second
         'connection' to be created by the JET engine
         'causing a blocking condition.
         strSQL = "UPDATE t_blocking SET f_dummy = 'updated' " & _
                  "WHERE f_pkey > 20"
         db.Execute strSQL
         CommitTrans

      End Sub
 



The code should appear to hang on the last db.Execute statement. Using the server administrative tools for the server that you are using, check the locking status on the t_blocking table. It should show the first connection blocking the second.

NOTE: Depending on the locking mechanisms of the server, this test may not cause a locking problem.



RESOLUTION
To work around the problem:

Keep transactions as small as possible. If this condition is encountered, check the locking on the server and limit the transaction to the point where blocking occurs.


Use the ODBC API. If you are using the 32-bit Visual Basic Enterprise Edition, use Remote Data Objects (RDO) or ActiveX Data Objects (ADO). When using RDO or ADO, you have complete control over connections and transactions on the connections, so you can avoid this problem.


Use SQLPassthrough queries that combine the blocking statements into one statement that does transactions within the SQL statement. This could be ad hoc SQL or precompiled into a stored procedure.





STATUS
This behavior is by design.



MORE INFORMATION
(c) Microsoft Corporation 1997, All Rights Reserved. Contributions by Troy Cambra, Microsoft Corporation.



Additional query words: kbdse kbDSupport kbVBp kbVBp500bug kbVBp600bug kbVBp400bug kbADO

Keywords : kbADO kbDAOsearch kbDatabase kbJET kbODBC kbVBp kbVBp400bug kbVBp500bug kbVBp600bug kbGrpDSVBDB kbGrpDSMDAC kbDSupport kbMDAC260
Issue type : kbprb
Technology : kbVBSearch kbAudDeveloper kbZNotKeyword6 kbZNotKeyword2 kbVB500Search kbVB600Search kbVBA500 kbVBA600 kbVB500 kbVB600 kbVB400Search kbVB400 kbVB16bitSearch

0
 

Expert Comment

by:beachbum714
ID: 7092672
Hey Moondancer

How about splitting the points to all that commented and closing the question?
0
 
LVL 1

Expert Comment

by:Moondancer
ID: 7092900
Great idea, thank you.  Points have been split.

Points for Crin -> http://www.experts-exchange.com/jsp/qShow.jsp?qid=20313762
Crin, please comment in the above link.

:) Moondancer - EE Moderator
0
 

Author Comment

by:FidelMike
ID: 7110456
Sorry I did not get back for grading. Beachbum714 looks to be the winner.  Thank You
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now