VBA ODBC Activity is crashing Excel

ScottDarth

New Member
Joined
Jun 25, 2020
Messages
1
Office Version
  1. 365
Platform
  1. Windows
I'm trying to write some code that will iterate through rows in an Excel spreadsheet (I'm using Excel 365 Version 1908). For each row, it'll check to see if that row's "unique identifier" exists in a database. It *happens* to be a DB2 database, but it could be any ODBC source. I'm trying to do this with ODBC, and my connection string works (I validated this by actually using "Get Data -> From Other Sources -> From ODBC," and successfully getting the data to load into a worksheet.)

However, I've added references to "Microsoft ActiveX Data Objects 2.8 Library" and "Microsoft ActiveX Data Objects Recordset 2.8 Library" to my project. I've then got the following code:

VBA Code:
Dim conn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim cmd As New ADODB.Command
Dim sqlTxt As String
Dim cmdTxt As String

Dim Row As Long
Dim Findex As Long
Dim Data As Worksheet

Dim rslt As String

cmdTxt = "ODBC;DSN=TCIS"
conn.Open (cmdTxt)
cmd.ActiveConnection = conn
cmd.CommandType = adCmdText
sqlTxt = "SELECT * FROM TCIS.PGMP WHERE UNIQUEID=1234567"
cmd.CommandText = sqlTxt
Set rs = cmd.Execute

Do Until rs.EOF

    rslt = rslt & "UNIQUE ID = " & rs("UNIQUEID") & " | CLIENT NAME = " & rs("CLIENTNAME") & vbCrLf
    rs.MoveNext
Loop

conn.Close

MsgBox rslt

When I run this, it consistently hangs and exits on the rs.EOF line. I've also tried just the following simpler thing:

VBA Code:
If rs.EOF and rs.BOF then fileIsEmpty = TRUE

Whenever it hits the rs.EOF thing, it just hangs up and exits. I mean, Excel shuts down, and then restarts, and tries to reload the spreadsheet.

What am I missing? What am I doing wrong? Why can't I get past this?
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Watch MrExcel Video

Forum statistics

Threads
1,113,931
Messages
5,545,091
Members
410,652
Latest member
Zot
Top