Debugging Error

H

hycho

Registered User.
Local time
Today, 18:44
Joined
Sep 7, 2011
Messages
59
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.
 
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.
 
hycho said:
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
Click to expand...

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.
 
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
 
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:
 
Thank you, Bob. Unreal the hoops one must jump through in camp M$. :confused:

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?
 
mdlueck said:
So which persuasion does Windows XP SP3 fall under... Windows 7 SP0?
Click to expand...
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.
 
boblarson said:
Yep, sort of. If you are running SP1 of Office 2010 or Windows 7 SP1 (if you have Office 2010 SP1...
Click to expand...

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.
 
mdlueck said:
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.
Click to expand...

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.
 
boblarson said:
If you have Office 2007 and Windows 7 SP1, you have that problem.
Click to expand...

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?
 
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.
 
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?
 
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.
 
boblarson said:
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
Click to expand...

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?
 
mdlueck said:
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?
Click to expand...
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.
 
boblarson said:
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.
Click to expand...

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. :D Thank you, Bob!
 
boblarson said:
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.
Click to expand...

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?
 
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...
boblarson said:
The database will compile when run. It does that automatically and needs to be able to do so.
Click to expand...
... 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"?
 
You must log in or register to reply here.

Similar threads

R
  • Question Question
Using vba to select a particular record in a combo box is not working properly
Replies
4
Views
286
cheekybuddha
cheekybuddha
B
Issue with migration to SQL Server and .Addnew
Replies
19
Views
724
Pat Hartman
P
A
Restrictions Update Message
Replies
17
Views
296
Gasman
Gasman
L
Consuming an API to retrieve data and store it in a Microsoft Access table.
Replies
3
Views
236
lbcurrier
L
N
VBA MS Access Error Handling
Replies
5
Views
305
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom