Excel VBA ADO SQL connection error - Could not find the object

Question

I got a brilliant answer to my previous question from @Ryan Wildry but I thought I'd ask a different question regarding the same code: here goes.

Background Info

I have a shared (network/server) Excel template file which is both the input file and the data source (although on different sheets). Let's call that Input.xltm.

The code basically picks up a input in a range on Input Sheet, takes the first two letters and finds the closest code from Code Sheet, then populates a UserForm ListBox with the top five results.

The problem

The problem comes when users set off the UserForm and the error usually returns:

Run-time error '-2147467259' The Microsoft Access database engine could not find the object 'C:\Users\user.name\Documents\Input1'. Make sure the object exists and that you spell its name and the path name correctly.......etc

I think it may have something to do with the fact Excel puts a number after the filename because it's a template file although I don't actually know!

The code

And here's the code:

Public MyConnection As New ADODB.Connection
Public MyRecordset  As New ADODB.Recordset

Private Sub UserForm_Initialize()
Dim ColumnName As String: ColumnName = "[Variant code]"
Dim SearchStr  As String: SearchStr = Left(Sheets("Input Sheet").Range("B4").Value2, 2)
Dim dbstring As String

dbstring = ThisWorkbook.FullName

Application.ScreenUpdating = False

If MyConnection.State <> adStateOpen Then
    With MyConnection
        .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbstring & _
                            ";Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1';"
        .Open
    End With
End If

If MyRecordset.State = adStateOpen Then MyRecordset.Close
MyRecordset.Open "Select top 5 " & ColumnName & " from [Code Sheet$] where " & ColumnName & _
                 " like '%" & SearchStr & "%'", MyConnection, adOpenForwardOnly, adLockReadOnly

Me.ListBox1.Clear
If Not MyRecordset.EOF Then MyRecordset.MoveFirst

Application.ScreenUpdating = True

Do Until MyRecordset.EOF
    Me.ListBox1.AddItem MyRecordset.Fields(0).Value
    MyRecordset.MoveNext
Loop

End Sub

I just need everyone who accesses the file through the server to be able to pick up the correct data source (which is only in the next sheet) and populate the ListBox.

I'd be thankful for any suggestions! Thanks

#UPDATE

I have checked, now if you open (and then save) the actual template file so there's no '1' after the file name, then the code works as expected. It's only when the template is opened normally and the number automatically appended that it stops working.

AFAIK you need to be working with a workbook that exists on the filesystem before you can connect to it. So that implies you can connect to the original xlst file, or you can connect to something based on it after you have saved it once. So you could possibly just hardcode the name of the xlst file in dbstring = "putXLSTFilenameHere". — YowE3K
– YowE3K, Commented Aug 19, 2016 at 8:34
@YowE3K Thanks - I get a different error now, "External table is not in the expected format" ! Would I have to reference the sheet as well? — jg2703
– jg2703, Commented Aug 19, 2016 at 8:42
If you are telling it to look in the XLST file, you will need to reference the sheet that contains the data in the XLST file, i.e. change [Code Sheet$] to whatever the sheet is in the XLST file. (Disclaimer: I have never used a template file, so I am just assuming that they are in the same format as a normal xlsx file, i.e. have sheets, etc, with sheet names, etc.) — YowE3K
– YowE3K, Commented Aug 19, 2016 at 8:46
@YowE3K - It's actually XLTM but yes, obviously it's all one file so the [Cost Sheet] does not need to change, I was referring to the Data Source where I've now hardcoded the filename into the dbstring. Putting Input.xltm!Cost Sheet returns the old error again, what should I use?? Thanks — jg2703
– jg2703, Commented Aug 19, 2016 at 8:50
I'm out of my depth then. If I get a chance I'll keep playing (I'm getting similar error messages) but hopefully someone else with more experience will help you out. — YowE3K
– YowE3K, Commented Aug 19, 2016 at 9:07

PaichengWu · Accepted Answer · 2016-08-19 07:15:18Z

1

It seems that you do not make early-binding for MyConnection and MyRecordset first.

You can make a late-binding by

step 1.

Change

Public MyConnection As New ADODB.Connection
Public MyRecordset  As New ADODB.Recordset

to

Public MyConnection As object
Public MyRecordset  As object

.

step 2.

Add

Set MyConnection = createobject("adodb.connection")
Set MyRecordset = createobject("adodb.recordset")

before If MyConnection.State <> adStateOpen Then

answered Aug 19, 2016 at 7:15

PaichengWu

2,6891 gold badge16 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

PaichengWu Over a year ago

What is the err msg? And Where does it stop?

jg2703 Over a year ago

As before; Run-time error, ..... could not find the object....... Input1 (note no file extension and it's pulling from my Documents, not the server path of the template file)

jg2703 Over a year ago

It just won't Show the UserForm, so it stops Initializing when it can't find the object. See update above

Collectives™ on Stack Overflow

Excel VBA ADO SQL connection error - Could not find the object

Background Info

The problem

The code

#UPDATE

1 Answer 1

step 1.

step 2.

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Background Info

The problem

The code

#UPDATE

1 Answer 1

step 1.

step 2.

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related