If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Opening & saving a database in '07 as an '03
I want to work from home... but I have Office '07 there and '03 at work. Can
I open an '03 Access database in '07 but force it to remain in a naitive '03 format... including all object library references etc. etc.? Just to see what would happen, I tried opening my database in '07, modified some VBA code, and saved it. It saved it as a .mdb (without me having to tell it to...). Now when I try to open it in '03, I get all kinds of error messages and things just not working right. It changed some object library references to newer versions, I get error messages like "missing dll" and "invalid syntax" even on functions that I didn't change. When I try to open the database window (which I don't want opening at startup) it just hangs. I've heard that holding the shift key while opening a database can bypass certain things... can someone clarify exactly what that does, and will it help me here? Thanks for any advice you can give, Jey |
#2
|
|||
|
|||
Opening & saving a database in '07 as an '03
Hi Jey,
Holding down the shift key while the database is opening will cause any startup options to be ignored, as long as the bypass key has not been previously disabled. This includes bypassing an Autoexec macro and/or a form set as the startup form. I suspect that you have one or more references that are currently marked as MISSING, in the copy that you edited in A2007 and attempted to re-open in A2003. Here are two excellent articles on the subject of missing references: Solving Problems with Library References (Allen Browne) http://allenbrowne.com/ser-38.html Access Reference Problems (Doug Steele) http://www.accessmvp.com/djsteele/Ac...nceErrors.html Tom Wickerath Microsoft Access MVP http://www.accessmvp.com/TWickerath/ http://www.access.qbuilt.com/html/ex...tributors.html __________________________________________ "Jey" wrote: I want to work from home... but I have Office '07 there and '03 at work. Can I open an '03 Access database in '07 but force it to remain in a naitive '03 format... including all object library references etc. etc.? Just to see what would happen, I tried opening my database in '07, modified some VBA code, and saved it. It saved it as a .mdb (without me having to tell it to...). Now when I try to open it in '03, I get all kinds of error messages and things just not working right. It changed some object library references to newer versions, I get error messages like "missing dll" and "invalid syntax" even on functions that I didn't change. When I try to open the database window (which I don't want opening at startup) it just hangs. I've heard that holding the shift key while opening a database can bypass certain things... can someone clarify exactly what that does, and will it help me here? Thanks for any advice you can give, Jey |
#3
|
|||
|
|||
Opening & saving a database in '07 as an '03
Hi Tom,
Yes, in the version that I edited at home and now want to open in A2003, the Excel 12.0 library is marked 'Missing'. In the copy of the database that I didn't take home, I have the Excel 11.0 library checked. (I have functions that export data to excel) One of the articles said to uncheck the missing library, get out of Access, then go back in and re-check it. I tried, but it won't let me uncheck the 'missing' Excel 12.0 library! Editing things in the database (tables etc.) doesn't seem to make it change that library, just when I open the VBA editor... so it seems to work fine in A2007 using the Excel 11.0 library. Do the 11.0 and 12.0 libraries have the same name & location? I think what it comes down to is can I edit the VBA code in A2007 without it automatically 'upgrading' to the Excel 12.0 library? I don't want to have to go in and mess around with libraries every time I want to give the database to someone using A2003!! Thanks, Jey "Tom Wickerath" wrote: Hi Jey, Holding down the shift key while the database is opening will cause any startup options to be ignored, as long as the bypass key has not been previously disabled. This includes bypassing an Autoexec macro and/or a form set as the startup form. I suspect that you have one or more references that are currently marked as MISSING, in the copy that you edited in A2007 and attempted to re-open in A2003. Here are two excellent articles on the subject of missing references: Solving Problems with Library References (Allen Browne) http://allenbrowne.com/ser-38.html Access Reference Problems (Doug Steele) http://www.accessmvp.com/djsteele/Ac...nceErrors.html Tom Wickerath Microsoft Access MVP http://www.accessmvp.com/TWickerath/ http://www.access.qbuilt.com/html/ex...tributors.html __________________________________________ "Jey" wrote: I want to work from home... but I have Office '07 there and '03 at work. Can I open an '03 Access database in '07 but force it to remain in a naitive '03 format... including all object library references etc. etc.? Just to see what would happen, I tried opening my database in '07, modified some VBA code, and saved it. It saved it as a .mdb (without me having to tell it to...). Now when I try to open it in '03, I get all kinds of error messages and things just not working right. It changed some object library references to newer versions, I get error messages like "missing dll" and "invalid syntax" even on functions that I didn't change. When I try to open the database window (which I don't want opening at startup) it just hangs. I've heard that holding the shift key while opening a database can bypass certain things... can someone clarify exactly what that does, and will it help me here? Thanks for any advice you can give, Jey |
#4
|
|||
|
|||
Opening & saving a database in '07 as an '03
Hi Jey,
One of the articles said to uncheck the missing library, get out of Access, then go back in and re-check it. I tried, but it won't let me uncheck the 'missing' Excel 12.0 library! This is unusual. I have seen in the past where there is sometimes a reference to the Microsoft Forms Library (version 2 [?]) that I've never been able to remove manually. The solution is to create a brand new database, and import all objects into it. I have explicit instructions in the lower half of page 3 of a Word document that I call "Access Links". You are welcome to download a zipped copy from this location: http://www.accessmvp.com/TWickerath/index.htm Editing things in the database (tables etc.) doesn't seem to make it change that library, just when I open the VBA editor... so it seems to work fine in A2007 using the Excel 11.0 library. You know, I'm honestly not sure if all of the references are fixed when first opening the database, or on-demand (ie. the first time a VBA function is called). In any case, if you ever use a common function in a query, such as Date(), Left, Right, InStr, etc., you would be making a call to VBA, in which case your references would be "fixed" for you. Do the 11.0 and 12.0 libraries have the same name & location? I believe they have the same name. The location is likely not the same. I think what it comes down to is can I edit the VBA code in A2007 without it automatically 'upgrading' to the Excel 12.0 library? I wouldn't be willing to bet any money on this. I don't want to have to go in and mess around with libraries every time I want to give the database to someone using A2003!! This is the exact reason that using late binding is the preferred option. Declaring variables as Objects is using Late Binding. Here are two examples: Early Binding (requires a checked reference to the Excel {version} Object Library) Dim xlApp As Excel.Application Late Binding (does not require a checked reference) Dim xlApp As Object It is easier to develop code using early binding, because you get the benefit of Intellisense. However, you get a more robust application by converting your code, once you have it working as desired, to late bound code because your code is not tied to a version specific object library. Tom Wickerath Microsoft Access MVP http://www.accessmvp.com/TWickerath/ http://www.access.qbuilt.com/html/ex...tributors.html __________________________________________ "Jey" wrote: Hi Tom, Yes, in the version that I edited at home and now want to open in A2003, the Excel 12.0 library is marked 'Missing'. In the copy of the database that I didn't take home, I have the Excel 11.0 library checked. (I have functions that export data to excel) One of the articles said to uncheck the missing library, get out of Access, then go back in and re-check it. I tried, but it won't let me uncheck the 'missing' Excel 12.0 library! Editing things in the database (tables etc.) doesn't seem to make it change that library, just when I open the VBA editor... so it seems to work fine in A2007 using the Excel 11.0 library. Do the 11.0 and 12.0 libraries have the same name & location? I think what it comes down to is can I edit the VBA code in A2007 without it automatically 'upgrading' to the Excel 12.0 library? I don't want to have to go in and mess around with libraries every time I want to give the database to someone using A2003!! Thanks, Jey |
#5
|
|||
|
|||
Opening & saving a database in '07 as an '03
Thanks!
That sounds like exactly what I needed to know. I'll try to remove all early binding from my code and hopefully that will fix it. Will it just be the Excel reference that I want to eliminate from the reference list? It's the only one that I checked myself. There were 5 others already checked: -VB for Applications -Microsoft Access 11.0 object library -OLE automation -Microsoft DAO 3.6 object library -Microsoft ActiveX Data Objects 2.1 library Jey "Tom Wickerath" wrote: Hi Jey, One of the articles said to uncheck the missing library, get out of Access, then go back in and re-check it. I tried, but it won't let me uncheck the 'missing' Excel 12.0 library! This is unusual. I have seen in the past where there is sometimes a reference to the Microsoft Forms Library (version 2 [?]) that I've never been able to remove manually. The solution is to create a brand new database, and import all objects into it. I have explicit instructions in the lower half of page 3 of a Word document that I call "Access Links". You are welcome to download a zipped copy from this location: http://www.accessmvp.com/TWickerath/index.htm Editing things in the database (tables etc.) doesn't seem to make it change that library, just when I open the VBA editor... so it seems to work fine in A2007 using the Excel 11.0 library. You know, I'm honestly not sure if all of the references are fixed when first opening the database, or on-demand (ie. the first time a VBA function is called). In any case, if you ever use a common function in a query, such as Date(), Left, Right, InStr, etc., you would be making a call to VBA, in which case your references would be "fixed" for you. Do the 11.0 and 12.0 libraries have the same name & location? I believe they have the same name. The location is likely not the same. I think what it comes down to is can I edit the VBA code in A2007 without it automatically 'upgrading' to the Excel 12.0 library? I wouldn't be willing to bet any money on this. I don't want to have to go in and mess around with libraries every time I want to give the database to someone using A2003!! This is the exact reason that using late binding is the preferred option. Declaring variables as Objects is using Late Binding. Here are two examples: Early Binding (requires a checked reference to the Excel {version} Object Library) Dim xlApp As Excel.Application Late Binding (does not require a checked reference) Dim xlApp As Object It is easier to develop code using early binding, because you get the benefit of Intellisense. However, you get a more robust application by converting your code, once you have it working as desired, to late bound code because your code is not tied to a version specific object library. Tom Wickerath Microsoft Access MVP http://www.accessmvp.com/TWickerath/ http://www.access.qbuilt.com/html/ex...tributors.html __________________________________________ "Jey" wrote: Hi Tom, Yes, in the version that I edited at home and now want to open in A2003, the Excel 12.0 library is marked 'Missing'. In the copy of the database that I didn't take home, I have the Excel 11.0 library checked. (I have functions that export data to excel) One of the articles said to uncheck the missing library, get out of Access, then go back in and re-check it. I tried, but it won't let me uncheck the 'missing' Excel 12.0 library! Editing things in the database (tables etc.) doesn't seem to make it change that library, just when I open the VBA editor... so it seems to work fine in A2007 using the Excel 11.0 library. Do the 11.0 and 12.0 libraries have the same name & location? I think what it comes down to is can I edit the VBA code in A2007 without it automatically 'upgrading' to the Excel 12.0 library? I don't want to have to go in and mess around with libraries every time I want to give the database to someone using A2003!! Thanks, Jey |
#6
|
|||
|
|||
Opening & saving a database in '07 as an '03
Hi Jey,
The first two references, Visual Basic for Applications and the Microsoft Access {version} object library, are required of all databases. Access will not allow you to remove these two references, even if you try. The next three references you listed, OLE automation, Microsoft DAO 3.6 object library and Microsoft ActiveX Data Objects 2.1 library are set by default for all new databases created using Access 2003. My feeling is that it is best to starve the references listing. If you don't need a reference, then uncheck it. This way, you don't consume RAM memory loading libraries that won't be used, and, more importantly, you help minimize the chances of encountering a MISSING reference error in the future, either by you or by someone else who is using an application that you developed. Here are two excellent articles on the subject of references: Solving Problems with Library References (Allen Browne) http://allenbrowne.com/ser-38.html Access Reference Problems (Doug Steele) http://www.accessmvp.com/djsteele/Ac...nceErrors.html So, how do you know if a reference is needed or not? The easiest way to determine this is to uncheck one reference at a time, click on the OK button to dismiss the Add References dialog box, and then attempt to compile your code: Debug | Compile {ProjectName} where {ProjectName} is the name of your VBA project. Before removing any references, make a backup copy of your database. You might even want to capture an image that lists the checked references (one can also run code to print this data out). Also, before removing any references, try compiling your code using the command shown above, just to verify that you do not have any pre-existing compile problems. You will know if your code compiles okay if you do not receive an error. Also, if you immediatly attempt to compile again, before making any change to code, you should see that the option is "greyed out" (unavailable). This is a good sign. Once you have code that compiles okay, then try removing the references one at a time, recompiling each time. If the code compiles okay, then you did not need the reference that you just removed. My guess is that you can remove the OLE Automation and the ADO (Microsoft ActiveX Data Objects 2.1 library) references without a problem. It is very common to find DAO code, so you may very well need that one, but you can test it in the same way to verify. I forgot to mention in my previous reply that your best chance of success is to use the lowest version of Access for all of your development work. For example, Access 2007 includes a new feature called embedded macros. The various wizards have been re-written to use this feature. So, if you use the wizards in A2007, and you then convert back to the A2000 or 2002/2003 file format, you may very well find command buttons or other functionality that simply doesn't work in the earlier version of Access. Tom Wickerath Microsoft Access MVP http://www.accessmvp.com/TWickerath/ http://www.access.qbuilt.com/html/ex...tributors.html __________________________________________ "Jey" wrote: Thanks! That sounds like exactly what I needed to know. I'll try to remove all early binding from my code and hopefully that will fix it. Will it just be the Excel reference that I want to eliminate from the reference list? It's the only one that I checked myself. There were 5 others already checked: -VB for Applications -Microsoft Access 11.0 object library -OLE automation -Microsoft DAO 3.6 object library -Microsoft ActiveX Data Objects 2.1 library Jey |
Thread Tools | |
Display Modes | |
|
|