By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,824 Members | 2,287 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,824 IT Pros & Developers. It's quick & easy.

Tracing Links Problem and Conversion Errors

JamesDC
P: 66
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
Feb 23 '07 #1
Share this Question
Share on Google+
29 Replies


ADezii
Expert 5K+
P: 8,636
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.
Feb 23 '07 #2

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Feb 23 '07 #3

JamesDC
P: 66
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
Feb 23 '07 #4

NeoPa
Expert Mod 15k+
P: 31,489
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).
Feb 26 '07 #5

JamesDC
P: 66
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:
Expand|Select|Wrap|Line Numbers
  1.  
  2. Option Compare Database
  3. Option Explicit
  4. Public Function LongMonth(Month As Integer)
  5. LongMonth = Choose(Month, "January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December")
  6. End Function
  7.  
  8. Public Function Lastday(Month As Integer, Year As Integer)
  9. If Year Mod 4 = 0 Then
  10.     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/")
  11. Else
  12.     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/")
  13. End If
  14. End Function
  15.  
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
Feb 27 '07 #6

NeoPa
Expert Mod 15k+
P: 31,489
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.
Feb 27 '07 #7

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Feb 27 '07 #8

NeoPa
Expert Mod 15k+
P: 31,489
Snap!
:D
Feb 27 '07 #9

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

It brings up the following section of code:

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub Report_Open(Cancel As Integer)
  5. Dim iOEE As Integer
  6. Dim iOldOEE As Integer
  7.  
  8. If [test].[Line #] = 1 Then
  9.     iOEE = [OEE] + iOEE
  10. End If
  11.  
  12. Report [test].txtOEE.Text = iOEE
  13. End Sub
  14.  
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.
Feb 27 '07 #10

NeoPa
Expert Mod 15k+
P: 31,489
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).
Feb 27 '07 #11

JamesDC
P: 66
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.
Feb 27 '07 #12

MMcCarthy
Expert Mod 10K+
P: 14,534
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub Report_Open(Cancel As Integer)
  5. Dim iOEE As Integer
  6. Dim iOldOEE As Integer
  7.  
  8. If [test].[Line #] = 1 Then
  9.     iOEE = [OEE] + iOEE
  10. End If
  11.  
  12. Report [test].txtOEE.Text = iOEE
  13. End Sub
  14.  
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
Feb 27 '07 #13

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Feb 27 '07 #14

NeoPa
Expert Mod 15k+
P: 31,489
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.
Feb 27 '07 #15

NeoPa
Expert Mod 15k+
P: 31,489
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.
Feb 27 '07 #16

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

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command176_Click()
  2.     ID.SetFocus
  3.     ID.Text = ID.Text + 1
  4.     Date.SetFocus
  5.     Date.Text = ""
  6.     ItemNum.SetFocus
  7.     ItemNum.Text = ""
  8.     LineNum.SetFocus
  9.     LineNum.Text = ""
  10.     ActualManHours.SetFocus
  11.     ActualManHours.Text = ""
  12.     ScheduledHoursOnLine.SetFocus
  13.     ScheduledHoursOnLine.Text = ""
  14.     Timebox.SetFocus
  15.     Timebox.Text = ""
  16.     Comments.SetFocus
  17.     Comments.Text = ""
  18.     ItemDescription.SetFocus
  19.     ItemDescription.Text = ""
  20.     LineDescription.SetFocus
  21.     LineDescription.Text = ""
  22.     ActualTotalCases.SetFocus
  23.     ActualTotalCases.Text = ""
  24.     ActualHoursOnLine.SetFocus
  25.     ActualHoursOnLine.Text = ""
  26.     DowntimeReason.SetFocus
  27.     DowntimeReason.Text = ""
  28.  
  29. End Sub
  30.  
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.
Feb 27 '07 #17

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Feb 27 '07 #18

NeoPa
Expert Mod 15k+
P: 31,489
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.
Feb 27 '07 #19

JamesDC
P: 66
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...
Feb 27 '07 #20

NeoPa
Expert Mod 15k+
P: 31,489
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).
Feb 27 '07 #21

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Feb 27 '07 #22

MMcCarthy
Expert Mod 10K+
P: 14,534
Snap again :D
Feb 27 '07 #23

JamesDC
P: 66
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...
Feb 27 '07 #24

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Feb 27 '07 #25

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Feb 27 '07 #26

NeoPa
Expert Mod 15k+
P: 31,489
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.
Feb 27 '07 #27

NeoPa
Expert Mod 15k+
P: 31,489
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.
Feb 27 '07 #28

JamesDC
P: 66
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...
Feb 27 '07 #29

NeoPa
Expert Mod 15k+
P: 31,489
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.
Feb 27 '07 #30

Post your reply

Sign in to post your reply or Sign up for a free account.