Error with back end

[Chrisopia]

I have created a databse system with a back end tables to make it network freindly. But it doesn't link the tables relative to the database but instead gives them a new pathway.

This is troublesome if I want to put the system on a computer network without access, it wont link. Is there a way to link the tables relative to the database rather than a full path name?

 

[HiTechCoach]

I have created a databse system with a back end tables to make it network freindly. But it doesn't link the tables relative to the database but instead gives them a new pathway.

This is troublesome if I want to put the system on a computer network without access, it wont link. Is there a way to link the tables relative to the database rather than a full path name?

With an Access back end the linked tables need the full path.

Note sure I follow how you are wanting to set his up.

The proper way to set up a multiple user database is:

1) the back end is in a network share where the user have read and write permissions.

2) the front end is copied to the local hard drive for each user. The front end should not be shared.

This is troublesome if I want to put the system on a computer network without access

Ummm... Without Access how will they be able to use your database?

Either the full Access retail version or the Access Runtime version will be require on each workstation that will open the front end database.

 

[gemma-the-husky]

BUT

for jet tables the path is stored in each linked table as part of the tabledef object, and is the connect property.
so what it actually stores is

;DATABASE=full path

so you can easily arrange to replace this full path, with the start of your database (which is currentproject.path) and the rest of the path


so
currentdb.tabledefs("sometable").connect = ";database=" & currentproject.path & "\" & relativepath

should get you close

 

[Chrisopia]

Ummm... Without Access how will they be able to use your database?

Either the full Access retail version or the Access Runtime version will be require on each workstation that will open the front end database.

Yes the computers will have runtime, not the full version of access. It is important the users cannot edit the database design.

My issue is that none of the computers on the network have the full version of access for that reason, only my computer which is not part of the network (and where the database is designed).

Somehow I need to manually tell access where the linked tables will be in the end - e.g. each front end will have a manually written path name for each linked table.

currentdb.tabledefs("sometable").connect = ";database=" & currentproject.path & "\" & relativepath

is this the solution gemma?

so I would have in the vba on database load for example:

currentdb.tabledefs("tblCustomers").connect = ";database=" & "\\Network\Main Files\Documents\database_be.aacdb"
currentdb.tabledefs("tblInvoices").connect = ";database=" & "\\Network\Main Files\Documents\database_be.aacdb"
currentdb.tabledefs("tblCategories").connect = ";database=" & "\\Network\Main Files\Documents\database_be.aacdb"

would that work???

 

[HiTechCoach]

I do all my database split. I regular relink the tables between test data and production data.

If you wan to protect the design the you also want to compile the front end into a MDE/ACCDE. Access also handles the MDE/ACCDE in a different manner. Unhandled errors will not cause Access to shutdown in an MDE/ADCCDE, etc. with the Access runtime With an MDB/ACCDB this is not true.

Note: You can run the full version of Access in runtime mode. I force all my front ends to be run this way.

When ready to deploy a new front end I will do the following:

1) relink the tales to the production back end.
2) Create a new MDE/ACCDE
3) copy the new fron en tot he server.
4) Update the version number so that the front ends will now they need to be updated

This may help: Splitting your Access database into application and data

 

[smig]

you can use

dim tbl as Dao.TableDef
for each tbl in currentdb.tabledefs
tbl.connect = ";database=" & \\Network\Main Files\Documents\database_be.aacdb
tbl.refreshlink
next tbl

 

[Chrisopia]

you can use

dim tbl as Dao.TableDef
for each tbl in currentdb.tabledefs
tbl.connect = ";database=" & \\Network\Main Files\Documents\database_be.aacdb[/URL]
tbl.refreshlink
next tbl

right :S so which part of that is the name of the table??

and when would this vba be triggered??

 

[smig]

tabledefs is the collection of tables
currentdb.tabledefs = collection of all tables in current db

tbl declared as a table object

this line will tell the code to do the task for ALL tables (table objects) in your db:
for each tbl in currentdb.tabledefs

in your code:
currentdb.tabledefs("tblCategories")
is a pointer to the table object named tblCategories


you will run this code in the OnOpen event of your loading form.

 

[Chrisopia]

Thankyou, I think I'm onto something.

I tried that tabledefs method and kept getting error #3219, saying the tbl.connect function is invalid??

Everywhere else on the internet has an extremely long winded play around with tbl.connect. What am I doing wrong??

 

[Guus2005]

I once had a similar problem. Paths where getting very long and to speed up things (a shorter path is faster than a long path i am told) i used a batch file to create a mapping, copy the frontend to the users temp directory and start the application.
The backend is on the newly mapped directory

@echo off

set APPNAME=EDB2
Set CurrentDir=%~DP0%

if exist P: subst P: /D
subst P: "F:\Somedir\SomeSubdir\PathToBE\OrderDatabaase"

if not exist %temp%\%AppName% md %temp%\%AppName% >NUL

echo ..
echo One moment please, programfiles are being copied !
echo ..
copy "F:\_ApplicationData\Frontends\EDB2\EDB2App.mdb" %temp%\%AppName%\EDB2App.mdb >NUL

Rem Startup application
%temp%\%APPNAME%\EDB2App.mdb

Additional advantage is when you are developing the application and you want to ship it to your customer, you could use the same backend path as the customer has. You only need to change the path in the batch file.

This way the backend tables are always on P:\BackEnd.mdb and you don't need to reconnect.

As for the error message #3219 you need to show the code you are using. Apparently you are doing something wrong.

HTH

 

[Chrisopia]

dim tbl as Dao.TableDef
for each tbl in currentdb.tabledefs
tbl.connect = ";database=" & \\Network\Main Files\Documents\database_be.aacdb
tbl.refreshlink
next tbl

This is the code i have used. I understand how it works but it just throws the error as I mentioned before.
And websites talking about tbl.Connect waffle on a lot about irrelevant things.

I've tried placing it within a Dim called Path, but it still throws the same error

My issue is that I need to tell access what the path is to connect to the backend when it's on the network because I have no access to the network and the entire system will just be a simple drag and drop jobby on the user computers.

I appreciate that there is only one copy of the backend and each front end will be manually copied onto each computer which will use it.

 

[Chrisopia]

I have found 2 useful snippets of code:

acCmdLinkedTableManager
acCmdImportAttachAccess

The first one actually runs the wizard to link tables. The second one runs the wizard to re-link tables.

these are all very good, but there are 2 main issues.

1) It will run everytime the database is loaded, so I will have to venture and find a code to say to run once and only once, or every time it errors e.g. cannot find the link file

2) The actual wizard itself isn't very user friendly, to instruct another person how to do it would be quite difficult, even in step by step. Hence the original drag and drop technique.

I guess it's back to square 1 and try to tell access where the link file is permanently. Unless you can think of a better reason to use either one of my snippets??

 

[HiTechCoach]

I would highly recommend using this code by a friend and fellow Access MVP with some assistance from other Access MVPs and gurus:

ReLinker: multi-BE, Label Table Description to show Connect info
* can't get much better and easier than this. It is very easy to add to an existing database.