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 'MSysCompactError'. 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 "DatabaseName". 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 MySysCompactError, I believe this related to my other errors. Once more this thing confuses me. It is a table fof 4 headings: Error Code, ErrorDescription, 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 MySysCompactError, I believe this related to my other errors. Once more this thing confuses me. It is a table fof 4 headings: Error Code, ErrorDescription, 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.

[NeoPa]

This is one of those funny functions and I've no idea what library it's from.
Mary

I would simply remove it.
It seems clear that the intention is to create a running sum and, although there is a better way nowadays, leaving the amended code in place doesn't seem like a big problem to me. It should get the OP past that particular problem at least.

[JamesDC]

I got rid of the Report problem with your advice NeoPa, and now I'm getting an error in another area of the code.

Private Sub Command176_Click()
    ID.SetFocus
    ID.Text = ID.Text + 1
    Date.SetFocus
    Date.Text = ""
    ItemNum.SetFocus
    ItemNum.Text = ""
    LineNum.SetFocus
    LineNum.Text = ""
    ActualManHours.SetFocus
    ActualManHours.Text = ""
    ScheduledHoursOnLine.SetFocus
    ScheduledHoursOnLine.Text = ""
    Timebox.SetFocus
    Timebox.Text = ""
    Comments.SetFocus
    Comments.Text = ""
    ItemDescription.SetFocus
    ItemDescription.Text = ""
    LineDescription.SetFocus
    LineDescription.Text = ""
    ActualTotalCases.SetFocus
    ActualTotalCases.Text = ""
    ActualHoursOnLine.SetFocus
    ActualHoursOnLine.Text = ""
    DowntimeReason.SetFocus
    DowntimeReason.Text = ""
 
End Sub
 

It's telling me that ItemNum is an non defined variable, I think this may have to do something with one of the tables, I'm looking into that now.

Also, as a general question, I know when I learned Java and C++ we were really pushed to comment on every bit of code we write. Is it the creator of this file, or is it programming with VB in access, that leads to no commenting in the file? I would think that comments relating where certain items relate to certain tables could be very helpful... but in all of the Access databases used by my company I can see no commenting in any of the code.

[MMcCarthy]

Also, as a general question, I know when I learned Java and C++ we were really pushed to comment on every bit of code we write. Is it the creator of this file, or is it programming with VB in access, that leads to no commenting in the file? I would think that comments relating where certain items relate to certain tables could be very helpful... but in all of the Access databases used by my company I can see no commenting in any of the code.

Access VBA code should be commented like any other code. I know what you mean though. I've troubleshooted on some huge databases with no comments and it's a nightmare. All you need to do to add a comment is to put an apostrophe at the beginning of a line of text.

Mary

[NeoPa]

I got rid of the Report problem with your advice NeoPa, and now I'm getting an error in another area of the code.

Pleased that helped.

It's telling me that ItemNum is an non defined variable, I think this may have to do something with one of the tables, I'm looking into that now.

Please let us know which line it failed on and the exact error message. I can't work out from what you say if this is simply that the code uses the .Text property again, or that the control [ItemNum] is not defined on the form.

Also, as a general question, I know when I learned Java and C++ we were really pushed to comment on every bit of code we write. Is it the creator of this file, or is it programming with VB in access, that leads to no commenting in the file? I would think that comments relating where certain items relate to certain tables could be very helpful... but in all of the Access databases used by my company I can see no commenting in any of the code.

I would always recommend commenting code. Having said that, it is probably more necessary to do so when working in a C related language than in a version of Basic, which typically is nearer to a natural spoken language than C is.
C was once notably described as a Read Only language.

[JamesDC]

Sorry about that,

the line that the error came up on is :
ItemNum.SetFocus

Where ItemNum is highlighted. The exact error is Compile Error: Variable Not Defined

I can't find any items on any of the forms that use Command176 when clicked. So I'm still looking...

[NeoPa]

Look for a command button whose name is Command176 (Don't rely on the text in the associated label but look for the Name of the control itself).

[MMcCarthy]

Sorry about that,

the line that the error came up on is :
ItemNum.SetFocus

Where ItemNum is highlighted. The exact error is Compile Error: Variable Not Defined

I can't find any items on any of the forms that use Command176 when clicked. So I'm still looking...

Open the form in design view that this code is written behind. You should be able to tell from the top display bar which form it is. The command button will be one of the buttons on this form. Look at the properties of each button and check the name property under the Other tab to find it.

Now look for ItemNum as a textbox name on this form.

Mary

[MMcCarthy]

Snap again :D

[JamesDC]

It's like I'm talking to only 1 person with the way your two replies keep coming back...

Anyways, I searched all the buttons, comboboxes, and text boxes and could not find any trace of a Command176 being used. I did find an old un-used form though that has a text field with control source: "Item Num"

I suspect this has something to do with "ItemNum" in the code, but the space is creating a problem...

[MMcCarthy]

It's like I'm talking to only 1 person with the way your two replies keep coming back...

Anyways, I searched all the buttons, comboboxes, and text boxes and could not find any trace of a Command176 being used. I did find an old un-used form though that has a text field with control source: "Item Num"

I suspect this has something to do with "ItemNum" in the code, but the space is creating a problem...

Comment out all the code with an apostrophe at the beginning of each line and this will stop the error. You can always visit it again if it turns out that something is no longer working.

Mary

[MMcCarthy]

It's like I'm talking to only 1 person with the way your two replies keep coming back...

Hey Ade -

Now I'm getting worried. If we start thinking alike we're in big trouble. :D

Mary

[NeoPa]

Open the form in design view that this code is written behind. You should be able to tell from the top display bar which form it is. The command button will be one of the buttons on this form. Look at the properties of each button and check the name property under the Other tab to find it.

Now look for ItemNum as a textbox name on this form.

Mary

Snap again :) (Ooops - didn't catch all the updates in-between. Now I have it's frightening how similar our answers are :().
Alternatively, to get to the design of the form whose code is displayed in the VBA window, hit Alt-F11. This should switch you across to the main Access window with that object selected.

[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).

I was hoping it wouldn't get to this stage, but in my recent experience, I had to comment out all offending code when I upgraded (I had to upgrade from 95 to 97 and then again on to 2K) and I only revisited the code to fix it all up when it was fully upgraded.
My problem was mainly due to CurrentDB not being liked by Access97. Access 2K liked it fine though so I just re-instated the lines when it got that far.
In your case you'll probably find that there's just a whole bunch of extraneous code which was just never tidied up properly (or at all). As the lady says - just comment it out unless and until you find you actually need it.

[JamesDC]

I've commented out that entire command176 block of code and now there are no errors on debugging. I'm going through the database now to determine if anything was hurt by doing this. So far everything is working fine...

[NeoPa]

Glad to hear that James.
Upgrading can be a headache as you've found out, but after the learning curve things do settle down a bit. Of course taking over someone else's sloppy work is never fun - it's hard enough when they are relatively tidy.