Run a function from a different DB

[crpjaviman]

Hello everyone,

I am trying to modify a database that will transfer tables to different DB.

The reason I need to do a transfer is because the end users for the destination DB do not have access to the same drive that I do. So because of that, I need to transfer the table to the their copy of the database in order for them to be able to use it.

I don't want to make this to complicated but here is some my code:

Function tester12()

Dim db As Database
Dim wkspc As Workspace

Set wkspc = DBEngine.Workspaces(0)
Set db = wkspc.OpenDatabase("c:\Sample_be.mdb&quot

The function is called "Tester" in the Mod "Transmod".

If I can get the function to run, that is where I have the transferdatabse method ready and able to go.

Any suggestions?

Thanks,
crpjaviman

 

[Rolliee]

Why do you have this in a module? I guess that is what you mean.

You can do this as a button on a form that has your databse bound to it - then open another database and add the records. You probably need some way to keep that from happening twice with the same data. Maybe you delete all the recs iin the foreign and then write new ones. Then no prob.

rollie@bwsys.net

 

[crpjaviman]

rolliee,

The transferdatabase module is set up to only transfer unique tables with a "For...Next".

The main reason that I have it in a module is because I want it to run in the background. I am going to have the tables transferred 3-4 times a day on a timer. I don't want to have to depend on an end user, including myself, to press a button.

Any other suggestions?

Thanks,
crpjaviman

 

[thornmastr]

Assume a database on drive S and a database on drive V. Occupants of Drive V have no way to see or interact with occupants of Drive S. Ocupants of drive S have no way to see or interact with occupants of Drive V.

A database on Drive V must pass a table (tblAgent) to override tblagent on a database on Drive S. the database on Drive V must pass this information to the database on Drive S if and only if the previous transmission has already occurred. The transfer is precipitated by a series of actions that a user periodically takes. It is not dependent on time or any measurable item. It simply happens at some point. It may be once a day, ten times a day, or only twice in a month.

What allows this to happen, and happen very well and with very little trouble is that items residing on Drive S and items on Drive V have access to drive T.

When whatever sequence of events on the database on V occur, that database uses the DIR function to see if T:\tblAgent.txt exists. If it does, that database does nothing because the information on T has not yet been processed by the database on drive S. however, if the file does not exist, the database on V uses a docmd.transfertext and sends tblAgent as a text file to drive T, and then continues happily unconcerned with the database on drive S.

When the database on drive S first opens, it opens a form hidden and goes on about its normal business, ie, opening a menu and waiting for something to happen. The invisible very tiny form acts as a holder for a timer that fires every 3 minutes. When the timer fires, the timer event looks for T:\tblAgent.txt. if it does not exist, the timer event exits and goes back to sleep peacefully for another three minute; or five minutes, or one hour or five hours, etc. but, it the file does exist, it immediately issues a SQL command to DROP tblAgent. It then does a docmd.transfertext using
T:\tblAgent.txt to build tblText. The last thing it does, before going to sleep, is delete
T:\tblAgent.txt.

Perhaps this will solve your problem.
Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[MichaelRed]

To paraphrase Mr. Berman, the two systems must have some media (e.g. Disc storage) which is commonly accessible to BOTH.

Use that location as an intermediate transfer point for the data. If there is no common media, there will be no transfer. The details of the ttransfer may vary depending on the common media, it is even concieveable for small data sets to transfer via e-mail or the venerable 'sneaker net', but even these include the "common media" requirement.


MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[crpjaviman]

I do apologize for not being able to respond before today, there was a tech problem. This is what I was trying to say:

The occupants of drive S do not have access to drive V, but occupants of drive V have access to drive S. Here lies the problem:

I have a BE database on drive V and this where I have the function saved. I need to start that function to transfer it's tables to the drive S database via a timer from a 3rd different database form on drive V.

I hope I didn't confuse anyone.

Let me try this explaination:

Database (drive V) form with timer set for 3 hours-> then

BE_Database (drive V) runs transferdatabase function for unique table-> then

FinalDatabase (drive S) receives tables.


The previous blue statements have been tested and they work, but from the red to the first blue statement is where I am stuck.

I hope this does not sound different than trying to explain my dilemna.

Any help would be appreciated.

Thanks,
crpjaviman

 

[oridge]

You can call your function by placing a call to your function on the "OnCurrent" event of the form that is used when the database is loaded.

Hope this helps!

 

[crpjaviman]

Hello everyone,

I have solved my problem with a little help. I found this code in one of the threads, as soon as I find the thread I will post it.

The code will open an Access session, minimized, opens the module and then runs the function.

I have it working on 5 different databases and when it finishes one database, it closes the database not the session.

Upto now, it's been working without too much bother to the end users.

Here is the code:

Dim app As Access.Application
Dim strDbPath, StrMacroName, file1 As String
Dim x As Integer

Set app = CreateObject("Access.Application&quot

file1 = "DatabaseName.mdb"

app.Modules.Application.Visible = False

strDbPath="S:\Database\" & file1
app.OpenCurrentDatabase strDbPath
app.DoCmd.OpenModule "TransferMod"
app.Run "TransferTables" 'Runs function
app.CloseCurrentDatabase

app.Quit ' If you don't need the session any longer
Set app = Nothing

I hope this will help someone else.

Thanks,
crpjaviman

 

[crpjaviman]

Here is the thread that I found the previous code. There are some helpful links with in it.

thread181-162265

thanks,
crpjaviman