code to open specific tables

[daniels012]

Is there code that can be written to make sure the proper tables are loaded with a file?
We have one of the computers on our network that holds the tables for a database we use.

We have a file called WorkOrder.mdb.
I want to be able to open the file on another computer in the office.
but when it opens, it needs to have the tables (or BackEnd file).

Is there code that can be added to the OnOpen event to make sure
we get the tables in this directory:
\\SIDONNA\SharedDocs\WorkOrderBE.mdb

Thank You in advance!
Michael

[CreganTur]

Why don't you just create links to the tables in your backend database?

[daniels012]

I am trying to setup a Microsoft Access Runtime package. Once it creates the Runtime version, you cannot change the tables. Therefore, I was looking for code to automatically OnOpen link the tables.
Then if I edit the FrontEnd of the database and resend the Runtime Access File, I would not have the same each issue each time. I would have the code to add the tables each time.

Thank You,
Michael

[daniels012]

Something like this maybe:
[VBA]DoCmd.DeleteObject acTable, "ClassificationTbl", "InvoiceTbl", "Proposals", "RequestTbl", "SalesmanSheetTbl", "TblBillTo", "TblHowWeGotJob", "TblSalesman", "TblWorkOrder", "TblFloorTbl"
[/VBA]
Then link a new one
(Here is where I have no idea what i am doing!!!)

[VBA]DoCmd.TransferDatabase acLink, "\\Sidonna\WorkOrderBE.mdb", , acTable,
"ClassificationTbl", "InvoiceTbl", "Proposals", "RequestTbl", "SalesmanSheetTbl", "TblBillTo", "TblHowWeGotJob", "TblSalesman", "TblWorkOrder", "TblFloorTbl"[/VBA]

Am I on or near the right track?????

Mcihael

[CreganTur]

I would suggest using ADO (ADOX) to setup your links for the tables.

The following code sample will link the Customer's table from an external database. You'll need to play around with it to get it to work for you- mainly change strDB to the filepath of your database, and then change strTable to the name of your desired table.

To do this with a bunch of tables I would suggest embedding the code to create a linked table inside a loop. Also create an array with the names of all of the tables you want to work with. Then run the loop, iterating through all of the table names you want to link.

[VBA]Sub LinkJetTable()
Dim cat As ADOX.Catalog
Dim linkTbl As ADOX.Table
Dim strDB As String
Dim strTable As String
On Error GoTo ErrorHandler
strDB = CurrentProject.Path & "\Northwind.mdb"
strTable = "Customers"
Set cat = New ADOX.Catalog
cat.ActiveConnection = CurrentProject.Connection
Set linkTbl = New ADOX.Table
With linkTbl
'name the new table and set its ParentCatalog property to the
'open catalog to allow access to the properties collection
.Name = strTable
Set .ParentCatalog = cat

'set the properties to create the link
.Properties("Jet OLEDB:Create Link") = True
.Properties("Jet OLEDB:Link Datasource") = strDB
.Properties("Jet OLEDB:Remote Table Name") = strTable
End With
'append the table to the tables collection
cat.Tables.Append linkTbl
Set cat = Nothing
MsgBox "The current database containes a linked table named " & strTable
Exit Sub
ErrorHandler:
MsgBox Err.Number & ": " & Err.Description
End Sub[/VBA]

HTH

[daniels012]

WOW!
I just don't know what alot of this means.
I understand to use your code that makes sense (sort of).
I have no idea how to begin to do the loop with all of the tables.
I included all of the tables and the directory on the network.
Is there more detail that I can give to get further assistance?
I am trying to do something that I am very GREEN at doing.

Thank You,
Michael

[CreganTur]

I'll rework the code for you if you need it, but first I have to ask this question again: why will it not work for you to create permanent links to the tables? Your reasoning above is that "you cannot change the tables." What do you mean by that exactly?

There's no reason to dynamically create and delete the exact same links every time the application is launched- this adds some overhead to your application that you can easily avoid by just creating real linked tables. If you cange a table in your backend database, then you will have to change the code to create the linked table anyway, which would require you to send out an updated version of your MOD application.

[daniels012]

I am attempting to create an Access 2007 Runtime packet.
When I create a packet, it will the WorkOrder.mdb file will not work
because the file that is made is based off of the file in it's original
network directory.
It being a runtime version, you cannot add/edit tables. Therefore,
my reasoning for the code.

I hope this is clearer.

Michael

[stanl]

Does your runtime intend only to link the tables, or does it replicate forms/queries/modules from the original .mdb?

If you are only linking tables I wouldn't even bother with a runtime, you could put together a .wsc file to create an .mdb from scratch then search out and link the appropriate tables using ADOX code as was suggested.

Otherwise the replication seems a bit wasteful... which not just map users to the original mdb and control it as multi-user

Stan

[daniels012]

Stan,
WOW! What does it all mean.
We are a very small company. I am very new to Access coding etc.
We have 1 computer in our office that has Microsoft Access 2007. This is where the database is.
We want to be able to enter data from other computers in our office to this networked computer.
So really I just want a "front end" so to speak to enter data into the proper database.

I am sorry if i sound ignorant to what you are saying, but I am ignorant to what you are saying. let me know if I can be clearer in what I am after.

thank You,
Michael

[CreganTur]

I understand that you cannot make any design changes to tables in the Access RunTime environment. I think we're having some communication problems, so I'm going to try to explain my thought process.

You want a front end database that you can replicate via RunTime, and then distribute to your users. You already have a backend database setup on a network drive- this is good, very good; for reasons explained later.

Your front end database needs to a completely seperate entity from the backend. It should contain no tables of its own- all of the tables must be linked to your backend database. Build all of the forms, queries, and other objects that you want your users to interact with into this front end database. Make your runtime out of this front end database.

The links to all of your tables will still be valid in your runtime database, as long as it is on a computer that is connected to the network drive that contains your backend database. Be sure you use the explicit filepath for your backend! (it looks like you already do this: you used the server's name, instead of a drive name, which can be different between users)

Now, I know you're going to say that changes can't be made to the tables, and you are correct IF you are talking about the runtime front end. You can make all of the changes you want to the backend, but you might have to recreate your runtime application and re-release it if the table changes you make add fields or change data types, or even if you add new tables. If you're expecting to make a lot of changes to your table structures, then you have a design problem that needs to be fixed. Table structure should not change very often, if at all, in an established, working database.

If you made table changes to the backend that would require you to rewrite a portion of the linking code you've been asking for, then you'd have to do the exact same thing: recreate and redistribute the runtime.

Creating a runtime that relies on established links removes a lot of overhead, as well as many, many chances for an error to occur.

Take a look at this forum post. They make the same suggestion for the exact same reasons, but they might explain it a little better that I have.

It's fine that you're new to Access; we were all new once. We're offering what we honestly think is the best answer to your problem.

[daniels012]

Ok
Let's see if I get it?
So I build my front end database on the computer with Access 2007.
My backend should have only tables!

The links to all of your tables will still be valid in your runtime database, as long as it is on a computer that is connected to the network drive that contains your backend database. Be sure you use the explicit filepath for your backend! (it looks like you already do this: you used the server's name, instead of a drive name, which can be different between users)

I don't think I do this??? If I do, I don't know I do? Where do I make the explicit path to the backend??

Then create my runtime based on this front end file.
Then copy the runtime package onto a flash drive.
Then paste the Runtime package onto a laptop in the office.
Then Run the runtime setup.
Then all should work fine.

Is this close?
Michael

[daniels012]

When I wrote the DoCmd samples, I made that up.
I don't do this.
I was asking in this post if I can do this and would it work!

Michael

[CreganTur]

I don't think I do this??? If I do, I don't know I do? Where do I make the explicit path to the backend??

It's the same thing as creating a linked table- you're just being very explicit; using the server name instead of the default drive name that would show up by default.

To do this:
Open your database
Click File->Get External Data->Link Tables
A file dialog window will appear
Instead of browsing for your database, type in the explicit filepath in the File Name field (\\SIDONNA\SharedDocs\WorkOrderBE.mdb)
Click Ok
Another window will appear, allowing you to select the objects you want to link
Once you complete this process you will have created your linked tables


When I talk about creating explicit links I mean typing out the true filepath, which includes the server name: \\SIDONNA\SharedDocs\WorkOrderBE.mdb.
The other option is implicit- this is where you use a drive name, like 'D', for example:
D:\SharedDocs\WorkOrderBE.mdb
Since it's a shared drive, it is possible that \\SIDONNA could be D:\ for you and F:\ for another user in your company. By being explicit in your filepath you remove this obsticle- your database will connect via the server name instead of relying on a drive name that could change.