Tracing Links Problem and Conversion Errors

[JamesDC]

Hey all,

I have two problems with trying to upgrade an access file, not written by myself.

This first is that I'm trying to change the items on a dropbox list, and they are linked with the following code:

SELECT DISTINCTROW [Processing Data].[ID], [Processing Data].[Number], [Processing Data].[Products] FROM [Processing Data];

My problem is that I cannot find any table called Processing Data. Are there other places that I could look to try to find this? Or is there a way to open the link from inside the properties box for the dropbox?

My second question is concerning these conversion errors that appeared in the Table menu when the database was converted from Access 200 to Access 2002. They seem to each contain a small error comment, and I'm not sure if I should delete them.

Thanks in advance for any help you can provide,

James

[ADezii]

Hey all,

I have two problems with trying to upgrade an access file, not written by myself.

This first is that I'm trying to change the items on a dropbox list, and they are linked with the following code:

SELECT DISTINCTROW [Processing Data].[ID], [Processing Data].[Number], [Processing Data].[Products] FROM [Processing Data];

My problem is that I cannot find any table called Processing Data. Are there other places that I could look to try to find this? Or is there a way to open the link from inside the properties box for the dropbox?

My second question is concerning these conversion errors that appeared in the Table menu when the database was converted from Access 200 to Access 2002. They seem to each contain a small error comment, and I'm not sure if I should delete them.

Thanks in advance for any help you can provide,

James

__1 This Table may have been intentionally Hidden. Tools ==> Options ==> View ==> Hidden objects ==> Yes
__2 You can Delete the Conversion Errors Table with no ill effect whatsoever. It is just a helpfull tool to let the User know what Errors were encountered during a conversion.

[MMcCarthy]

Hey all,

I have two problems with trying to upgrade an access file, not written by myself.

This first is that I'm trying to change the items on a dropbox list, and they are linked with the following code:

SELECT DISTINCTROW [Processing Data].[ID], [Processing Data].[Number], [Processing Data].[Products] FROM [Processing Data];

My problem is that I cannot find any table called Processing Data. Are there other places that I could look to try to find this? Or is there a way to open the link from inside the properties box for the dropbox?

Hi James,

[Processing Data] could be a query. To find it...in the properties window of the combobox in the row source click in the value box (where the select statement is written) and a button with dots will show on the right. Click this will open the query window representing this query.

My second question is concerning these conversion errors that appeared in the Table menu when the database was converted from Access 200 to Access 2002. They seem to each contain a small error comment, and I'm not sure if I should delete them.

I wouldn't delete them unless you are sure that all issues have been dealt with.

Did you compile the database from the VBA editor after converting it?

Mary

[JamesDC]

Thanks to both of you,

It was indeed a hidden Table, I should've thought of that :S.

I'm just going to leave the errors there now since they don't affect anything. As far as how I converted it... I'm not really sure. It was done my Access when I first opened the file with Access 2002 after my company upgraded.

Thanks again,

James

[NeoPa]

In that case it is worth your while going through the errors and making sure all issues have been dealt with. Any issues not dealt with are very likely to bite you on the 4rse later on :(
To be sure that it is in a reliable state I would also recommend compiling the database completely before continuing. This will highlight any errors in the code that it discovers can't work. This is a very handy tool for finding and fixing errors.

Good luck with the task.
My nether regions are currently a little sore as I just discovered some more problems related to a database upgrade I did recently to A2K (And I did compile and test etc before releasing it).

[JamesDC]

Hey all,

I went through the errors in my database, and in total there were 63 error tables created, named Conversion Errors 1-63.

The early tables have only one error:

Module: There were compilation errors during the conversion or enabling of this database.

There is only one module in this database and it's the code for determining a leap year:

 
Option Compare Database
Option Explicit
Public Function LongMonth(Month As Integer)
LongMonth = Choose(Month, "January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December")
End Function

Public Function Lastday(Month As Integer, Year As Integer)
If Year Mod 4 = 0 Then
    Lastday = Choose(Month, "1/31/", "2/29/", "3/31/", "4/30/", "5/31/", "6/30/", "7/31/", "8/31/", "9/30/", "10/31/", "1/30/", "12/31/")
Else
    Lastday = Choose(Month, "1/31/", "2/28/", "3/31/", "4/30/", "5/31/", "6/30/", "7/31/", "8/31/", "9/30/", "10/31/", "1/30/", "12/31/")
End If
End Function

So I don't know why I keep getting this error. It is present in all of the conversion error tables.

ABout halfway through and continuing to the end the following two errors occur:
Table: Object Name: MSysObjects: -1611: The Microsoft Jet database engine stopped the process because you and another user are attempting to change the same data at the same time.

this one makes me believe that everytime more than one person tries to access the database this error is generated.

The third error is:
Table: Object Name: MSysObjects: Error Description
-1611: The Microsoft Jet database engine could not find the object 'MSysCompactErr or'. Make sure the object exists and that you spell its name and the path name correctly.

And I don't have a clue on this one :(.

Could anyone please provide some clarity for me on this issues,

Thanks,

James

[NeoPa]

If you have compilation errors you need to compile it explicitly (Debug / Compile Project from your VBA window).
If you can't figure out any particular problem post the code of the module it flags as well as the line it highlights and the error message and we'll see if we can make any sense of it.

[MMcCarthy]

Hi James

The module looks fine but in this case it may be referring to a form module which is the code behind the form. In the VBA editor window go to Debug - Compile "DatabaseNa me". This will highlight any errors in the code.

Mary

[NeoPa]

Snap!
:D

[JamesDC]

When I run the debug I get this error:
Compile Error: Invalid Use of Property

It brings up the following section of code:

Option Compare Database
Option Explicit

Private Sub Report_Open(Cancel As Integer)
Dim iOEE As Integer
Dim iOldOEE As Integer

If [test].[Line #] = 1 Then
    iOEE = [OEE] + iOEE
End If

Report [test].txtOEE.Text = iOEE
End Sub

With the word Report highlighted in the 2nd last line, is this a coding issue? I also have no idea what this section of code is for. The person who put this database together was very sloppy, there are unused tables and forms everywhere, so I suspect this is just a leftover testing code. But that's just how I feel.

[NeoPa]

This code will create a running sum in your Report (except it won't as the .Text bit at the end is an error).
It would be interesting to know what the Test object is that it refers to, but if you remove the .Text bit it should work without error.
Missed a bit earlier - I don't know where the word Report comes in at the start of that line. It should probably be removed too (I assume you've copied and pasted this in - it looks a little bit like a retype gone wrong to be honest).

[JamesDC]

I still get the same error when I remove the .Text part of the code.

Also, I found a hidden table called MySysCompactErr or, I believe this related to my other errors. Once more this thing confuses me. It is a table fof 4 headings: Error Code, ErrorDescriptio n, ErrorRecid, ErrorTable. There is one record in this table: -1003, Invalid Argument, ą, Processing Report (in the same heading order).

ErrorRecid seems to only accept binary values according to design view, but its value is that weird ą value.

[MMcCarthy]

Option Compare Database
Option Explicit

Private Sub Report_Open(Cancel As Integer)
Dim iOEE As Integer
Dim iOldOEE As Integer

If [test].[Line #] = 1 Then
    iOEE = [OEE] + iOEE
End If

Report [test].txtOEE.Text = iOEE
End Sub

I've never seen a piece of code like this. The Report would indicate trying to return something from a function but this is a procedure. It's possible that this is a function available in some library that is not available to the current database but as I've never seen it I can't make a guess at what library contains that function.

Mary

[MMcCarthy]

Snap!
:D

You got there seconds before me. ;)

This is one of those funny functions and I've no idea what library it's from. I would suggest giving Nico and ADezii a shout as they may be familar with it.

Mary

[NeoPa]

I still get the same error when I remove the .Text part of the code.

Also, I found a hidden table called MySysCompactErr or, I believe this related to my other errors. Once more this thing confuses me. It is a table fof 4 headings: Error Code, ErrorDescriptio n, ErrorRecid, ErrorTable. There is one record in this table: -1003, Invalid Argument, ą, Processing Report (in the same heading order).

ErrorRecid seems to only accept binary values according to design view, but its value is that weird ą value.

Sorry, I noticed (and updated my post) too late that the word Report needs to be removed from the line too.
I assume that the TextBox actually exists on the report.