Can I set relative paths for linked tables instead of absolutepath?

[Hyko]

Hello.

If I link a table to my Access DB from another Access DB the absolute path
to this DB is used. When I upload the database to the server, where I use
the databases as data source for a web application it cannot find this path
of course.

How can I change the path of the connected table so that it is relative
(both DBs are in the same directory)? Or how can I change the path at all
before I upload it to the server where I cannot influence it anymore?

Or is there any other possibility to link this table e.g. by System-DSN? As
far as I tried it now it is not possible that way.

Thanks in advance for any hints or suggestions,

Heiko

 

[Amy Blankenship]

I think what you'll have to do is throw an ASP page on the server that does
Response.Write Server.MapPath("somefile")

Use that to figure out what directory structure you'll need, then duplicate
that on your local machine. Now, open the database in that new directory
and relink all the files.

When you upload the files, everything should work.

I think it's silly myself that they don't make relative linking easier, but
what do I know.

HTH;

Amy

 

[Tim Ferguson]

If I link a table to my Access DB from another Access DB the absolute
path to this DB is used. When I upload the database to the server,
where I use the databases as data source for a web application it
cannot find this path of course.

As long as the (front end) mdb knows, or can find out, the site of the
back end file, you can write a short subroutine that will update the
..connect property of the relevant tabledefs:

strConnect = ";DATABASE=" & strPathToMDBFile
For p = 0 To db.TableDefs.Count - 1
Set tdf = db.TableDefs(p)
If tdf.Attributes And DB_ATTACHEDTABLE Then
If tdf.Connect <> strConnect Then
tdf.Connect = strConnect
tdf.RefreshLink
End If
End If
Next p

Note that this is uses the DAO library: it's probably possible using ADOX
but I can't imagine why anyone would want to...

All the best


Tim F

 

[Hyko]

@ Amy,
your solution worked. By accident I had a D:/ volume on my computer as
well...

Thank you very much for the hint!

@ Tim Ferguson
Thank you very much for your script, too. I am happy that it works now.

 

[Amy Blankenship]

I don't think that would work, because a web application does not have a
"front end" as you are used to thinking of it, and usually the server either
won't have Access installed or you can't get Remote Desktop or other similar
way to run the Access file to get it to connect itself. So you need to make
sure the connection is pointed correctly before you upload.

Hope this clarifies;

Amy

 

[Tim Ferguson]

I don't think that would work, because a web application does not have
a "front end" as you are used to thinking of it, and usually the
server either won't have Access installed or you can't get Remote
Desktop or other similar way to run the Access file to get it to
connect itself.

Point taken.

The way to do it would be to run the ReattachTabledefs as the last thing
after testing and before converting to an MDE and shipping, then.

I guess?


Tim F

 

[Amy Blankenship]

I don't think it makes much sense to make it into a MDE for upload to a
server to run ASP pages and the like against it, since that would mean you
couldn't download it and edit it with the new, dynamically added, data in
place.

So what Heiko is doing is determining what the path is on the server *is*
and replicating that path in place locally, so that everything can be
attached properly before upload. But if you're not a web (back-end)
developer, this is probably not a situation you'll understand completely
unless or until you need to handle it yourself.

<Rant>Microsoft panders too much, IMO, to people who use Access locally over
a network and makes it overly difficult to use Access as a web backend,
because they want you to use SQL Server as the web back-end.</Rant>

Hope this clarifies;

Amy

 

[Tim Ferguson]

<Rant>Microsoft panders too much, IMO, to people who use Access
locally over a network and makes it overly difficult to use Access as
a web backend, because they want you to use SQL Server as the web
back-end.</Rant>

I have to confess that I don't really understand the problem: it seems
that the OP is making it a lot more complex than I would have imagined.
What it seems to be is:

1) web clients make calls to

2) an asp (or php or cf or whatever) application on the server, which

3) calls data from a jet mdb which has attached tabledefs pointing

4) another mdb somewhere else where the tables are.

It's step 3 that I don't get, and it's also the one that seems to be
causing the problem. Shouldn't the asp code just read the stuff straight
from the actual data tables -- isn't that what ADO was invented for?

Failing that, is there any reason the ASP code can't update the
tabledefs?

Granted, I am just coming to learn about this myself, and our hosting
solution means that it will have to be WAMP rather than any kind of MS
data access, so perhaps I am just missing something.

All the best


Tim F

 

[Amy Blankenship]

I have to confess that I don't really understand the problem: it seems
that the OP is making it a lot more complex than I would have imagined.
What it seems to be is:

1) web clients make calls to

2) an asp (or php or cf or whatever) application on the server, which

3) calls data from a jet mdb which has attached tabledefs pointing

4) another mdb somewhere else where the tables are.

It's step 3 that I don't get, and it's also the one that seems to be
causing the problem. Shouldn't the asp code just read the stuff straight
from the actual data tables -- isn't that what ADO was invented for?

Say, for instance, you had a training database which had both student
information (completion, etc.) and course data up to and including the
content of the course. Say, then, that during development of the course
you'd developed Access forms to make data entry easier for the course
content. Then at that point you could choose to split the database so that
the content portion could be downloaded and edited while the student data
continued to accept completion information. This would avoid the need to
have to build web-based data entry toold since you already have tools that
will do the job.

Now, if you wanted to have a query that referenced information that was
contained in both sides (completion for a student that displayed the course
name, for instance), you'd need to reference tables on BOTH sides of the
linked database. They'd have to know about each other, or the query would
fail.

Add into that if you had stored queries that "lived" on only one side of the
database, then they'd REALLY have to know about each other. But even a
query from outside would fail if the two sides were not linked.

Failing that, is there any reason the ASP code can't update the
tabledefs?

Yes. ASP code can't SEE the tabledefs. You need a running Microsoft Office
runtime of some sort to be able to use Office automation. ASP can act on
non-System tables through SQL, and that's about the extent of it. I think
you can choose to expose your system tables, but I don't think that's wise
for a web application.

Granted, I am just coming to learn about this myself, and our hosting
solution means that it will have to be WAMP rather than any kind of MS
data access, so perhaps I am just missing something.

Hope this clarifies;

Amy

 

[Tim Ferguson]

Now, if you wanted to have a query that referenced information that
was contained in both sides (completion for a student that displayed
the course name, for instance), you'd need to reference tables on BOTH
sides of the linked database.

This is where we part company. When you split a database all the data are
still in one file. Anyone who tries messing about with tables in
different mdb files is surely making making a rod for his own back.

Yes. ASP code can't SEE the tabledefs.

Tommy rot. Both DAO and ADOX can manipulate the tabledef properties
directly, and either can be used in ASP classic. Even I know that much: I
have several vbs routines that manage data that were snitched straight
from ASP. I can't imagine that there isn't a dot-Net library that will do
the same task; there is far too many established jet databases to drop
it.

Office runtime of some sort to be able to use Office automation.

OLE automation-? That just has to be the worst possible solution, with
the possible exception of using SendKeys().

I am tempted to install the asp-dot-net beta just to test this out,
because it's beggaring my imagination.

All the best


Tim F

 

[Amy Blankenship]

This is where we part company. When you split a database all the data are
still in one file. Anyone who tries messing about with tables in
different mdb files is surely making making a rod for his own back.

The point of the linked database question was that the user asked how to
reconnect it. You asked for one possible reason to do it. I provided one.
You may never have encountered a situation that makes it necessary or
useful. That doesn't mean you never will, or that others haven't.

Tommy rot. Both DAO and ADOX can manipulate the tabledef properties
directly, and either can be used in ASP classic. Even I know that much: I
have several vbs routines that manage data that were snitched straight
from ASP. I can't imagine that there isn't a dot-Net library that will do
the same task; there is far too many established jet databases to drop
it.

Cool. Please post. I'm sure the original poster would be very interested.
I'm certainly interested, as I have this task ahead of me.

OLE automation-? That just has to be the worst possible solution, with
the possible exception of using SendKeys().

I am tempted to install the asp-dot-net beta just to test this out,
because it's beggaring my imagination.

Ouch. Can't have your poor imagination on the street corner with a cup out,
can we?



-Amy

 

[Hyko]

Am 25.11.2005 22:01 Uhr schrieb "Amy Blankenship" unter

The point of the linked database question was that the user asked how to
reconnect it. You asked for one possible reason to do it. I provided one.
You may never have encountered a situation that makes it necessary or
useful. That doesn't mean you never will, or that others haven't.

The point is, that I have two different web applications on one server which
have a Access DB each. Both applications do different tasks but use in some
points the same data structure (in my case commodities). Now I do not want
to duplicate this structure and duplicate the effort to keep both
structures up-to-date as well, but want to have it stored and edited in the
one application and used in both. There is of course the possibility to
build a second ADODB-connection to retrieve this structure, but in some
cases for reporting, I need to directly JOIN these tables with others, which
is only possible if all tables are in the same DB, virtually at least.

Cool. Please post. I'm sure the original poster would be very interested.
I'm certainly interested, as I have this task ahead of me.

Yes that would be great.

Heiko

 

[Tim Ferguson]

Cool. Please post. I'm sure the original poster would be very
interested. I'm certainly interested, as I have this task ahead of me.

Actually, from Hyo's post downthread, it looks as though these linked
tables really belong in one mdb file. Still, here is the old fashioned
DAO version:

' use dao to update the tabledef connect strings
'
' simplified script to update tabledef links using ADOX
' No error trapping! For demonstration only!!
'

'
' DAO Objects
'
dim dbe ' dbengine
dim db ' database
dim ws ' workspace
dim tdf ' tabledef

dim connectString ' connection string
dim mdbPath ' path and file of mdb file

const dbAttachedTable = &H40000000

' these consts should really be parameterised...
mdbPath = "C:\My Data\uncle.mdb"
connectString = ";DATABASE=C:\Access\db1.mdb"


' make a new dbengine to hold everything
Set dbe = CreateObject("DAO.DBEngine.36")


' open the database exclusively and read-write
set db = dbe.Workspaces(0).OpenDatabase(mdbPath, True, False)

' iterate the tabledefs
for each tdf in db.TableDefs
' we only want attached tables
if tdf.Attributes And dbAttachedTable Then
' helpful for debugging
wscript.echo "Updating " & tdf.Name
' set it and force the update
tdf.Connect = connectString
tdf.RefreshLInk

end if
next ' tdf

' tidying up
db.Close

'### end of script ###


and here is the ADOX version, which I just put together as an exercise...

' use ado to update the tabledef connect strings
'
' simplified script to update tabledef links using ADOX
' No error trapping! For demonstration only!!
'
' Tim Ferguson 2005
'
'
' These consts should really be parameterised!
'
const pathToFrontEnd = "C:\Documents and Settings\Ferguson\My Documents
\My Data\uncle.mdb"
const pathToBackEnd = "C:\Documents and Settings\Ferguson\My Documents
\Programming\Access\copy of db1.mdb"

'
' Don't believe the property "Jet OLEDB:Create Link" as it's used
' for querydefs too, and setting a path on them raises an error
'
' Poke the new address straight into the Link Datasource property
'
const propertyName = "Jet OLEDB:Link Datasource"

'
' ADOX objects
'
dim cat ' as catalog
dim tbl ' as table
dim prp ' as property

'
' Obvious
set cat = createobject("ADOX.Catalog")

' Obvious too
cat.activeconnection = "Provider=Microsoft.Jet.OLEDB.4.0" & _
";Data Source=" & pathToFrontEnd

' iterate through all the tables,
for each tbl in cat.tables
' look for a non-empty property
' all Access tables and querydefs appear to have this property
' set, even if it is only to an empty string
set prp = tbl.properties(propertyName)
if len(prp.Value)>0 Then

' warming for the user
wscript.echo "updating " & tbl.Name
' poke in the new value
prp.Value = pathToBackEnd

end if

next ' tbl

' all done; close down
set cat = nothing

'### end of script ###


Hope it helps


Tim F

 

[Amy Blankenship]

Thanks. Very useful.

Actually, from Hyo's post downthread, it looks as though these linked
tables really belong in one mdb file. Still, here is the old fashioned
DAO version:

' use dao to update the tabledef connect strings
'
' simplified script to update tabledef links using ADOX
' No error trapping! For demonstration only!!
'

'
' DAO Objects
'
dim dbe ' dbengine
dim db ' database
dim ws ' workspace
dim tdf ' tabledef

dim connectString ' connection string
dim mdbPath ' path and file of mdb file

const dbAttachedTable = &H40000000

' these consts should really be parameterised...
mdbPath = "C:\My Data\uncle.mdb"
connectString = ";DATABASE=C:\Access\db1.mdb"


' make a new dbengine to hold everything
Set dbe = CreateObject("DAO.DBEngine.36")


' open the database exclusively and read-write
set db = dbe.Workspaces(0).OpenDatabase(mdbPath, True, False)

' iterate the tabledefs
for each tdf in db.TableDefs
' we only want attached tables
if tdf.Attributes And dbAttachedTable Then
' helpful for debugging
wscript.echo "Updating " & tdf.Name
' set it and force the update
tdf.Connect = connectString
tdf.RefreshLInk

end if
next ' tdf

' tidying up
db.Close

'### end of script ###


and here is the ADOX version, which I just put together as an exercise...

' use ado to update the tabledef connect strings
'
' simplified script to update tabledef links using ADOX
' No error trapping! For demonstration only!!
'
' Tim Ferguson 2005
'
'
' These consts should really be parameterised!
'
const pathToFrontEnd = "C:\Documents and Settings\Ferguson\My Documents
\My Data\uncle.mdb"
const pathToBackEnd = "C:\Documents and Settings\Ferguson\My Documents
\Programming\Access\copy of db1.mdb"

'
' Don't believe the property "Jet OLEDB:Create Link" as it's used
' for querydefs too, and setting a path on them raises an error
'
' Poke the new address straight into the Link Datasource property
'
const propertyName = "Jet OLEDB:Link Datasource"

'
' ADOX objects
'
dim cat ' as catalog
dim tbl ' as table
dim prp ' as property

'
' Obvious
set cat = createobject("ADOX.Catalog")

' Obvious too
cat.activeconnection = "Provider=Microsoft.Jet.OLEDB.4.0" & _
";Data Source=" & pathToFrontEnd

' iterate through all the tables,
for each tbl in cat.tables
' look for a non-empty property
' all Access tables and querydefs appear to have this property
' set, even if it is only to an empty string
set prp = tbl.properties(propertyName)
if len(prp.Value)>0 Then

' warming for the user
wscript.echo "updating " & tbl.Name
' poke in the new value
prp.Value = pathToBackEnd

end if

next ' tbl

' all done; close down
set cat = nothing

'### end of script ###


Hope it helps


Tim F