Tools>Reference in VBA Project

[jstevens]

I have a workbook that utilizes a connection string to a SQL database and it works just fine. The challenge I'm having is when I migrate the VBA code to my 'Personal.xlsb' file and encounter an error on the code line: Dim cn As ADODB.Connection.

I receive a compile error: User-defined type not defined.

I have checked the Tools>Reference of the 'Personal.xlsb' file and it shows that Microsoft ActiveX Data Objects Recordset 6.0 Library is checked.

I tried closing Excel, rebooting and repairing MSoft Office but nothing has resolved the challenge. Normally I have the 'Personal.xlsb' file hidden but did try running the code while the file was visible.

Your suggestions are appreciated.

[HansV]

That is very weird - Microsoft ActiveX Data Objects Recordset 6.0 Library is the correct library.
Also, if it works in a standard workbook it should work in PERSONAL.XLSB too. Apart from being loaded automatically, it is not inherently different from other workbooks.
Try the following:
- Copy all code that refers to ADODB to a text file.
- Delete all modules that contain ADODB code.
- Clear the reference to Microsoft ActiveX Data Objects Recordset 6.0 Library.
- Quit and restart Excel.
- Set the reference to Microsoft ActiveX Data Objects Recordset 6.0 Library.
- Create a new module and copy the code from the text file into it.
- Select Debug > Compile Personal.

[snb]

Test:

Code: Select all

With CreateObject("ADODB.recordset")

end with

and

Code: Select all

CreateObject("ADODB.connection")

end with

[jstevens]

Hans/snb,

I tried both your suggestions and encountered the same challenge.

I did resolve the error message by:
1. Export all VBA modules from the Personal.xlsb file
2. Remove Personal.xlsb file from the START folder
3. Created a new Personal.xlsb file with no VBAProject modules
4. Import the BAS files generated from Step 1
5. Rename the Personal.xlsb file. Something like myStuff.xlsb

It would appear that renaming the file resolved the challenge.

Thank you for your suggestions.