Debugging Error

[hycho]

I've found Jon K's database/VBA codes to be very useful. However, since I switched to a new computer, I am no longer able to run his codes. To find out more about his database, please look at the thread below:

http://www.access-programmers.co.uk/forums/showthread.php?p=1172983#post1172983

So, when I try running the VBA code, I would get this error message:

Microsoft Visual Basic
Run-Time error '-2147221164 (80040154)':
Class not registered

And when I hit the debug button, the blue underline section of the code below is highlighted in yellow.

Does anyone know how I can resolve this issue? Do I need to download/register something?


Public Function Conc(Fieldx, Identity, Value, Source) As Variant
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim SQL As String
Dim vFld As Variant

Set cnn = CurrentProject.Connection
Set rs = New ADODB.Recordset
vFld = Null

SQL = "SELECT [" & Fieldx & "] as Fld" & _
" FROM [" & Source & "]" & _
" WHERE [" & Identity & "]=" & Value

' open recordset.
rs.Open SQL, cnn, adOpenForwardOnly, adLockReadOnly

' concatenate the field.
Do While Not rs.EOF
If Not IsNull(rs!Fld) Then
vFld = vFld & ", " & rs!Fld
End If
rs.MoveNext
Loop
' remove leading comma and space.
vFld = Mid(vFld, 3)

Set cnn = Nothing
Set rs = Nothing

' return concatenated string.
Conc = vFld
End Function


Thanks.

 

[hycho]

If anyone can write a better concatenate code than above, I will be happy to use the new method as well, since the current method does not fit exactly what I need to do.

Here's the data I have:

ID Language
1 English
1 Spanish
2 English
2 French

Here's the output I want (notice I now have two language columns instead of one):

ID Language1 Language2
1 English Spanish
2 English French

Thanks for your help.

 

[mdlueck]

I've found Jon K's database/VBA codes to be very useful. However, since I switched to a new computer, I am no longer able to run his codes. ... So, when I try running the VBA code, I would get this error message:

Microsoft Visual Basic
Run-Time error '-2147221164 (80040154)':
Class not registered

On the new computer, please verify that the same VBA \ Tools \ References are checked / enabled as on the old computer. I suspect you are missing ADO related items.

 

[hycho]

I tried your method, and the setting looks the same when comparing my last computer to my new computer.

FYI, another moderator in the queries forum has given me the solution below, but I can't quite figure out the instructions, and I am working with him to solve the issue.

Thanks...

------------------------------
Response from moderator

Looks like you need to set a reference to ADO, but if it is already set, then it may have to do with something Microsoft caused by changing the registry entries for ADO.

See here for more about that:
http://social.msdn.microsoft.com/For...6-34f11c6b5a13
http://social.msdn.microsoft.com/Fo...s/thread/3a4ce946-effa-4f77-98a6-34f11c6b5a13

 

[mdlueck]

Yea yea... I recall that type of nonsense years ago. Or more specifically when MDAC versions were jumping numbers in pretty quick pace. Having to deal with checking MDAC versions and and and... :banghead:

 

[boblarson]

The answer I gave in this post
http://www.access-programmers.co.uk/forums/showpost.php?p=1173317&postcount=28

the actual easy fix for the reference problem is just to compile (run DEBUG > COMPILE) on a computer that uses Windows 7 SP1 if the database is going to be used there, or do it on Windows 7 no SP if that is where it is goinb to be used. The problem comes when mixing the two.

 

[mdlueck]

Thank you, Bob. Unreal the hoops one must jump through in camp M$.

I will keep that sharp spot handy in my thoughts.

Bad enough to have to deal with A2007 / A2010 incompatibilities... let alone it mattering what OS Access happens to be running on!

So which persuasion does Windows XP SP3 fall under... Windows 7 SP0?

 

[boblarson]

So which persuasion does Windows XP SP3 fall under... Windows 7 SP0?

Yep, sort of. If you are running SP1 of Office 2010 or Windows 7 SP1 (if you have Office 2010 SP1 you are also under the same situation as if on Windows 7 SP1) then you have to ensure that you compile on the compatible machine before deploying.

 

[mdlueck]

Yep, sort of. If you are running SP1 of Office 2010 or Windows 7 SP1 (if you have Office 2010 SP1...

Oh, is this only an A2010 quirk? Does A2007 not exhibit this problem running on Windows 7 SP1?

You seemed only to talk about A2010 in your reply.

 

[boblarson]

Oh, is this only an A2010 quirk? Does A2007 not exhibit this problem running on Windows 7 SP1?

You seemed only to talk about A2010 in your reply.

No, you didn't understand.

If you have Office 2010 SP1 you have that problem.

If you have Windows 7 SP1 you have that problem.

If you have Office 2007 and Windows 7 SP1, you have that problem.

If you have Office 2010 SP1 and Windox XP, you have that problem.

Etc.

If you have EITHER of those Office 2010 SP1 OR Windows 7 SP1, you have that problem.

 

[mdlueck]

If you have EITHER of those Office 2010 SP1 OR Windows 7 SP1, you have that problem.

Ohhhh, so there are two sticky spots! I understand now. Thank you for clarifying.

 

[mdlueck]

If you have Office 2007 and Windows 7 SP1, you have that problem.

I just thought of...

I can handle this situation by detecting Win 7 SP1+ in my startup batch file (and copy a Win7SP1+ specific FE DB in that case), but what will happen with the legacy database I am replacing. That is an Access .MDB file shared on the file server! No separate FE/BE, nadda!

What if someone accesses the old database from a computer with Win 7 SP1 / Access 2007 SP3... I would assume one of two things, either they will get error messages similar to the OP of this thread, or by chance the DB will get "magically upgraded" (If someone thinks to start poking around as they think the database is "messed up") and I suppose no longer work with Windows XP computers. Yes?

 

[boblarson]

The way it works is this. It needs to be COMPILED (DEBUG > COMPILE PROJECT) on a machine which has the same SP level (either 1 or None) to work on the same type of machines. The other machines will work too as long as you do the same thing on one of them.

 

[mdlueck]

But in a mixed OS environment, shared .MDB file on the file server, as far as I know the Win7 upgrade has not touched team members yet, so we fine for the moment.

I am assuming we would not be fine running Windows XP against the shared .MDB file if someone on a Windows 7 SP1+ machine fixes the database by compiling it.

So I assume the old application is facing a "big bang upgrade" where either everyone stays on Windows XP, or EVERYONE is upgraded to Windows 7 AND the database gets recompiled.

Is that correct?

 

[boblarson]

Yes, I think you have it. Except you can be on Windows 7 and not have a problem between XP and 7 as long as you don't install SP1 for Windows 7 and don't install SP1 for Office 2010. If any of those situations were to have SP1 for Windows or Office 2010, you would either need to maintain two master files or make sure everyone is upgraded at the same time.

 

[mdlueck]

Yes, I think you have it. Except you can be on Windows 7 and not have a problem between XP and 7 as long as you don't install SP1 for Windows 7 and don't install SP1

That is not a long term solution... remaining SP'less. Plus, corporate IT is in charge of rolling out SP's, not I.

Anyway, another possibility occurred to me... how about decompiling the legacy version of the application and giving strict orders that no one ever compile it. Is it just compiled databases that have troubles with SP1?

 

[boblarson]

Anyway, another possibility occurred to me... how about decompiling the legacy version of the application and giving strict orders that no one ever compile it. Is it just compiled databases that have troubles with SP1?

The database will compile when run. It does that automatically and needs to be able to do so. I don't mean to create ACCDE/MDE files, but just simple compilation of the code. It has to do that. So, when it does that, it must be done on a machine which matches what it will be running on or else the problem will occur.

Now, just as additional information. If you do not use any ADO in your databases, all of this does not apply to you and you would not have to do any of this.

 

[mdlueck]

Now, just as additional information. If you do not use any ADO in your databases, all of this does not apply to you and you would not have to do any of this.

Oh, fffeeewww!!!! The old version I am replacing uses VERY simplistic VBA... I do not recall a single DB call in its aprox 1000 LOCs. Thank you, Bob!

 

[mdlueck]

Now, just as additional information. If you do not use any ADO in your databases, all of this does not apply to you and you would not have to do any of this.

So did Microsoft rev ADO / MDAC again and slip out a higher build in SP1's? Perhaps checking the MDAC version would be a reliable way to determine which FE DB to deploy to the target workstation?

 

[mdlueck]

Besides my lingering question of "Did Microsoft ship a higher build version of MDAC which caused this bang-upgrade scenario again?"

Would skipping the final compile step of my DB cleanup process ...

NT Command Script and Documented Steps to Decompile / Compact / Compile an Access DB
http://www.access-programmers.co.uk/forums/showthread.php?t=219948

... be a valid deployment scheme to use and support both Window XP and Windows 7 SP1 machines with the same Access FE DB? As you state...

The database will compile when run. It does that automatically and needs to be able to do so.

... Access will compile it on-the-fly if it is found decompiled, then would just allowing Access to have to always do the compile step on-the-fly be a viable decision and arrive again at a single FE DB file which would work "everywhere"?