Copying Modules

[NewfieGolfer]

Hi,
I'm trying to copy modules from one database to another database. The problem i'm having is that i'm getting that nasty "Error accessing file" message when i try and open the modules after copying them. I'm doing the copying using VBA (I need to copy using VBA). I know that in order to get rid of that message i need to copy to the other database, open the modules in the other database, compile them, save and then close the other database. How would i do this using VBA?

Thanks,
NG

 

[thornmastr]

Why would you ever want to copy code modules from database to database unless it is the idle results of some crazed graduate student taking revenge on the freshman class? If you need to use the functions, all you have to do is reference the data having the modules in the database that does not have the modules.

Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[NewfieGolfer]

What about the database, once created, is copied to a completely different computer? I know, all i have to do is copy and paste the file, as is, to the other computer and not create a completely different database, but i'm creating two databases from a third database. One database has the forms, queries, reports, and LINK TABLES from the other created database. The tables database becomes the back end on the network, and the other created database becomes the front end to be used on many other computers. So, i kinda need the code in the created database as well.

The above would work if this was gonna be on just one computer.

NG

 

[thornmastr]

This is interesting. Either you are making a simple issue overly complex, or I need more coffee which is distinctly possible.

If you are using a front end back end combination you have users with each user having the FE on their PC and the BE sits comfortably on the server.

Now, you have some 3rd database which contains procedures all your users need. If I have pegged the problem, put a copy of the 3rd database in a shared folder on the network. Create a reference to that database on the front end. Since that reference is referencing a common path, your users front ends will have the reference and call procedures from the third database with no problem.
Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[NewfieGolfer]

My last resort would be to put the 3rd database on the network. I'm afraid that someone who shouldn't access this database will cause more problems if they happen to fall upon it while browsing the network files. I guess I could set up limited user access to this 3rd database.

I'll give this a try. But, having that third database off the network would be preferred.

Thanks,
NG

 

[NewfieGolfer]

One thing I forgot to mention, the 3rd database will be creating many different front ends, and back ends. So the procedure calls would have to be set up in the front ends when they are created by the 3rd database.

Thanks
NG

 

[thornmastr]

Consider using Access security to disallowing changes to design on that 3rd database, and allowing access to tables only via queries.

Or create a 4th database containing only the required modules and use it as the 3rd database. Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[jsolutions]

NG - this is actually a known problem from Microsoft. The cause has several factors to it and only one solution. After you have imported your modules from one database (db1) to another database (db2), you must recomiple db2. NOTE you must do this before you do anything else. So, do not open a form, query, report, table...nothing. If you have any startup code, bypass it.

Once in the database go to tools...macro...vb Editor. On the VB menu bar, select Debug...Compile databasename. This should alleviate the problem. Be aware, you will probably even find some problems that you didn't know existed - like code you created with potential syntax errors and tied to a button, then deleted the button and left the code.

NOte: the database that already has reported this erro is now useless (at least in my experience). You need to create a new database and reimport the modules, then follow the steps above.

By the way, the article which tells the factors involved that make this happen is located at

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q304548.

Simple rule of thumb is to always do the steps above when you import or copy modules.

Let me know if you have any questions.

Jay

 

[thornmastr]

I think this bug has been fixed. I use A2K2 and have no problem copying/importing modules at all. Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[jsolutions]

I didn't see what version NG is using. If he is getting this error, I am guessing it is Access 2k.

Good to hear that it is fixed in 2k2. I work with many clients who have 2k so I experience the problem from time to time. Have never had to do it in 2k2.

 

[NewfieGolfer]

Jay,
I'm aware of this problem. And, i knew how to fix the problem if i was to manually open the database, goto debug, compile, etc. But, my problem is that i need to do this procedure via Visual Basic Code from another database.

Just a note, it's interesting that they fixed this problem with A2K2 and didn't put out a fix for A2K. This problems seems to be fairly common with many people who use A2K. Is there a fix available? Does anyone know?

Thanks,
NG

 

[thornmastr]

From what i understand it was fixed in the last service release for A2K and A2k2. you might check MSDN and be sure you have the latest A2K SR. Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[jsolutions]

NG (and thornmastr) - first my apologies. I read the original post, the read all the replies and totally missed the fact that you (NG) already new about the recompile.

As for the fix for A2K, I have not seen one. The Q article doesn't mention any. If you do find one, let me know.

However, in answer to your question about compiling through VBA - I did some digging and found
application.RunCommand acCmdCompileAllModules
Since you are importing, there is also
application.RunCommand acCmdCompileandSaveAllModules.

The only question would be where you are starting the code from (in the importing database or the exporting database). From the importing database, you could simply test the iscompiled property of the project. (See

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/off2000/html/acproIsCompiled.asp)

If you are starting the code from the exporting database, you would need to create an application object refering to the importing application. (See :

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/off2000/html/acobjApplication.asp)

Hopefully though, Thornmastr is correct on a patch existing. That would make life much easier.

By the way, I have never used the code. Based on your requirements, I dug it up in MSDN. Hope it works. Let me know if you have any questions.

Jay

 

[thornmastr]

Jay,

Thanks for the information. It was very interesting. Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[NewfieGolfer]

Quick question. How do you call procedures in another database? Here's the catch, how do you do this NOT using VBA?

NG

 

[thornmastr]

The same way you do from Access. I assume you mean from a form or a query. The exact same way. As long as the other database is rerenced Access will find it. Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[NewfieGolfer]

Ok, but how do I reference the database with the modules without using VBA?

NG

 

[thornmastr]

NG,

I don't know exactly where you are trying to get with this so let me approach it from an example. Supposing you have a form, and a field on that form gets its default value from a function on the referenced database and the name of that function is def1. Then for the default value of that field on the form you would insert
=def1().

If I'm totally off in some other direction, tell me what are your criteria? Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[NewfieGolfer]

If I do the above I'll get an error that the procedure can't be found. It's because the module with the procedure is in another database.

Here's what i gotta do (The database with the modules will be on the network, we'll call it Module DB)

1) Open Database
2) Click a button.
3) Make a couple of inputs.
4) User clicks a button and two databases are created. One with the tables, the other with the forms,queries,and reports (like i talked about previously) and link tables.
The tables database is on the network and the other database is the front end used by many users.

5) The event procedures in the forms on the front end database must be able to call the functions in the Modules Database.

That's it. I probably should have said all this earlier

Thanks,
NG

 

[thornmastr]

Have you checked that the second created db, the one with the forms, etc, has a reference to db3; the one on the network (not the backend). Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com