Setting path through VBA

  • Thread starter Afrosheen via AccessMonster.com
  • Start date
A

Afrosheen via AccessMonster.com

Good morning, and thank you for reading this.

I have some hard code that I would like to change in to variable code.

20 strPathFilename_OriginalBEDB = "C:\NCI Roster\RosterBE\Roster 04-22-
01.mdb"
30 strPathFilename_TemporaryBEDB = "C:\NCI Roster\Backups\Roster 04-22-
01.bak"

Is there a way where the C:\NCI Roster\RosterBE\Roster 04-22-01.mdb can be
changed? The routine that comes with this I can use in other programs but I
have to hard code the statements.

Your help would be appreciated.

Thanks
 
J

John Spencer

Currentdb.Name returns the full path and name of the database.

If you need the full path of a BACKEND database, you can parse it out of the
connect string of a link in the front end.

Dim strPath as String

strPath = Currentdb().TableDefs("Name of Linked Table").Connect

If you don't want to use a specific name then you can step through all the
tables until you find one that has a connect string and use that



John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
A

Afrosheen via AccessMonster.com

Thanks for helping me out. This is what I used:
I did put the statement in a FE form.

The Roster 04-22-01.mdb is the name of the FE and BE.

Dim strPath As String
strPath = CurrentDb().TableDefs("Roster 04-22-01.mdb").Connect

When running it, I got an error: "Item not found in this collection."
Did I do something wrong?


John said:
Currentdb.Name returns the full path and name of the database.

If you need the full path of a BACKEND database, you can parse it out of the
connect string of a link in the front end.

Dim strPath as String

strPath = Currentdb().TableDefs("Name of Linked Table").Connect

If you don't want to use a specific name then you can step through all the
tables until you find one that has a connect string and use that

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Good morning, and thank you for reading this.
[quoted text clipped - 12 lines]
 
J

John Spencer

Roster 04-22-01.mdb is not the name of a linked table. it is the name of a
database.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Thanks for helping me out. This is what I used:
I did put the statement in a FE form.

The Roster 04-22-01.mdb is the name of the FE and BE.

Dim strPath As String
strPath = CurrentDb().TableDefs("Roster 04-22-01.mdb").Connect

When running it, I got an error: "Item not found in this collection."
Did I do something wrong?


John said:
Currentdb.Name returns the full path and name of the database.

If you need the full path of a BACKEND database, you can parse it out of the
connect string of a link in the front end.

Dim strPath as String

strPath = Currentdb().TableDefs("Name of Linked Table").Connect

If you don't want to use a specific name then you can step through all the
tables until you find one that has a connect string and use that

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Good morning, and thank you for reading this.
[quoted text clipped - 12 lines]
 
J

John W. Vinson

Thanks for helping me out. This is what I used:
I did put the statement in a FE form.

The Roster 04-22-01.mdb is the name of the FE and BE.

Dim strPath As String
strPath = CurrentDb().TableDefs("Roster 04-22-01.mdb").Connect

When running it, I got an error: "Item not found in this collection."
Did I do something wrong?

Yes - you used the name of the *database* (a container for multiple tables and
other things) instead of the name of *a table*. Try

strPath = CurrentDb.Tabledefs("tblEmployees").Connect

replacing tblEmployees by the name of a table that you know to exist in the
backend and to be linked to the frontend.

Do note that it would be very unusual to have your backend AND frontend .mdb
files both have the same name. I've never tried it, and I suppose it would
work, but I'd find it really confusing!
 
A

Afrosheen via AccessMonster.com

Hi John, I copied and pasted your statement and changed the table name and I
still got the error. I'm going to try and rename the BE table and see what
happens. It may be the name or because I have spaces in between, I don't know.


I'll get back after I make the changes and let you know if it worked or not.
It may take me a couple of days.

Thanks again for your help..
Thanks for helping me out. This is what I used:
I did put the statement in a FE form.
[quoted text clipped - 6 lines]
When running it, I got an error: "Item not found in this collection."
Did I do something wrong?

Yes - you used the name of the *database* (a container for multiple tables and
other things) instead of the name of *a table*. Try

strPath = CurrentDb.Tabledefs("tblEmployees").Connect

replacing tblEmployees by the name of a table that you know to exist in the
backend and to be linked to the frontend.

Do note that it would be very unusual to have your backend AND frontend .mdb
files both have the same name. I've never tried it, and I suppose it would
work, but I'd find it really confusing!
 
J

John W. Vinson

Hi John, I copied and pasted your statement and changed the table name and I
still got the error. I'm going to try and rename the BE table and see what
happens. It may be the name or because I have spaces in between, I don't know.

WHOA!!!!!

Disrupting your entire database by changing the name of a table (requiring
changes to all the forms, reports, queries and domain functions that reference
the table) is WAY overkill.

What's the name of the table?
Please post your code.
It is NOT THAT DIFFICULT!!!
 
A

Afrosheen via AccessMonster.com

I was just searching on how to relink the tables, but I'll wait.

This is the code:

Dim strPathFilename_OriginalBEDB As String
Dim strPathFilename_TemporaryBEDB As String
Dim blnKeepBack As Boolean
Dim strPath As String
strPath = CurrentDb.TableDefs("Roster 04-22-01").Connect

MsgBox strPath
Exit Sub

20 strPathFilename_OriginalBEDB = "C:\NCI Roster\RosterBE\Roster 04-22-
01.mdb"
30 strPathFilename_TemporaryBEDB = "C:\NCI Roster\Backups\Roster 04-22-
01.bak"

In this code, line 20 sets up the string for the back end file
Line 30 sets up the string to make a backup of the BE to this folder.

I set a break Msgbox to see what the string looks like.

I didn't know if you needed the actual back up function or not. I got the
original code from Ken Snell's web sight.
 
A

Afrosheen via AccessMonster.com

Good morning.
As of 8pm last night I haven't done anything yet. I've stopped after I saw
the WHOA...

Thanks for your help.
 
A

Afrosheen via AccessMonster.com

Sorry John, I had a brain cramp. I changed the database name to one of the
table names in the BE and this is what I got:

;DATABASE=C:\NCI Roster\RosterBE\Roster 04-22-01.mdb

I guess that's the start of what I was looking for.
That will take care of line 20
20 strPathFilename_OriginalBEDB = "C:\NCI Roster\RosterBE\Roster 04-22-
01.mdb"
30 strPathFilename_TemporaryBEDB = "C:\NCI Roster\Backups\Roster 04-22-
01.bak"
Now I need to change the name to equal line 30.

Sorry for all the problems. It's been one of those days.
 
J

John W. Vinson

Sorry John, I had a brain cramp. I changed the database name to one of the
table names in the BE and this is what I got:

;DATABASE=C:\NCI Roster\RosterBE\Roster 04-22-01.mdb

I guess that's the start of what I was looking for.
That will take care of line 20
20 strPathFilename_OriginalBEDB = "C:\NCI Roster\RosterBE\Roster 04-22-
01.mdb"
30 strPathFilename_TemporaryBEDB = "C:\NCI Roster\Backups\Roster 04-22-
01.bak"
Now I need to change the name to equal line 30.

Sorry for all the problems. It's been one of those days.

So where do you stand with this now? Does the Connect string get what you need
taken care of? You do know about the Name function (to rename files)?
 
A

Afrosheen via AccessMonster.com

The statement of ;DATABASE=C:\NCI Roster\RosterBE\Roster 04-22-01.mdb

gave me an error that the original database could not be found at this
location.

The VBA is:

Dim strPathFilename_OriginalBEDB As String
Dim strPathFilename_TemporaryBEDB As String
Dim blnKeepBack As Boolean

Dim strPath As String
strPath = CurrentDb.TableDefs("tbl_roster").Connect

20 strPathFilename_OriginalBEDB = strPath
30 strPathFilename_TemporaryBEDB = "C:\NCI Roster\Backups\Roster 04-22-
01.bak"

Call CompactBackendDatabaseFile_Custom(strPathFilename_OriginalBEDB,
strPathFilename_TemporaryBEDB, True)


I have the back end on drive c:\NCI Roster\BE\Roste 04-22-01.mdb
The front end is on drive c:\NCI Roster\FE\Roste 04-22-01.mdb

After I get the backend to work correctly then I want to come up in line 30
similar to line 20.

What would be your thoughts about putting "C:\NCI Roster\Backups\Roster 04-22-
01.bak"
or both statements in a configure table? Just wondering. Maybe it will save a
lot of problems.

Again, thanks for your help.
Sorry John, I had a brain cramp. I changed the database name to one of the
table names in the BE and this is what I got:
[quoted text clipped - 10 lines]
Sorry for all the problems. It's been one of those days.

So where do you stand with this now? Does the Connect string get what you need
taken care of? You do know about the Name function (to rename files)?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top