Run-time Error on Method 'Open' of object '_Connection'

[ell_]

Hi, all

I have this one file that several users have to run. It can run from my end perfectly but not from other users' end (have tried with 2 users so far).

This error has been prompted:
Annotation 2020-11-11 154710.jpg

And the line highlighted is at:

Please Login or Register  to view this content.

The tough part here is that this issue happens to some other people except me. With my limited experience, I'm not really sure how to debug this issue. Perhaps anyone who are used to this kind of error can give some insights?

Thanks!

[Kaper]

My first concept is that probably on your computer you have the reference to the ADO library and other users don't have it.

Open VBA Editor (Alt+F11) and look into Tools->References

You probably will see something like "Microsoft DAO 3.6 Object Library" with checkmark next to it. And other users probably don't have it checked.

[ell_]

Hi, Kaper

I've compared the other users' References with mine, and we only ticked this:
Annotation 2020-11-11 193049.jpg

I did not tick the object library you mentioned but still manage to run the program, but not for other users unfortunately.

[romperstomper]

Your code is late bound, so you don't need a reference. You do appear to have a typo in the extended properties - it should be:

Please Login or Register  to view this content.

not:

Please Login or Register  to view this content.

If that doesn't help, do you know what version of Office the problem machines have on them?

[ell_]

Your code is late bound, so you don't need a reference

Hi, Rorya

May I know what late bound means?

I have corrected the typo, still does not work on the other users. And our Office version is 2010, all of us.

[romperstomper]

You declared your variables as Object - that's late binding.

Are you all opening the same format workbooks? Your code is for xlsb files.

[ell_]

Are you all opening the same format workbooks? Your code is for xlsb files.

Yes indeed, they should be using the same format workbooks.

[ell_]

If that doesn't help, do you know what version of Office the problem machines have on them?

I just realized something, I found that my Office 2010 version is different:

Attachment 703759

Meanwhile other users' version is:

Attachment 703760

But in order for me to persuade my IT guy to somehow 'downgrade' other users' version to match with mine, I might need to pinpoint whether this is the actual cause. As what my IT dept mentioned, all versions under Microsoft 2010 have been standardized.

I wonder if can this cause the issue I'm facing? From my understanding, other users' version is the latest one compared to mine.

Any insight would be much appreciated. Thanks!

[romperstomper]

I can't see either of those attachments, I'm afraid. You need to use the Go Advanced button, then Manage attachments to properly attach images.

[ell_]

Oh, my bad. Didn't notice that.

For my excel version, it's the image my_excel_sp1 (Version 14.0.06023.1000)

Meanwhile, for the failed users', it's the image other_users_excel as attached (Version 14.0.07015.1000)

[romperstomper]

It seems odd to me that you'd have different versions in a corporate setting anyway!

The error is usually down to a bad connection string (but you've corrected that) or missing OLEDB providers. You might ask IT or the users to check in the registry and make sure there is a HKEY_CLASSES_ROOT\Microsoft.ACE.OLEDB.12.0 key there.

[ell_]

The error is usually down to a bad connection string (but you've corrected that) or missing OLEDB providers. You might ask IT or the users to check in the registry and make sure there is a HKEY_CLASSES_ROOT\Microsoft.ACE.OLEDB.12.0 key there.

I have checked their registry, and they do have the key mentioned I'm getting more confused now.

[romperstomper]

I suggest you try some simple code that opens a connection to one specific workbook using that provider, and see if it works. I'd also suggest using early binding in case you get a more descriptive error message.