June 24, 2011 at 5:13 am
Hi All,
I have recently moved an Access 2003 database from one drive to another, it contains a macro that uses CurrentDb in the VBA to get the path of the database.
When i run the code the CurrentDb is still set to the old path, is there a way to update the database so that the VBA recognises the new location?
Thanks in advance...
Matt
June 24, 2011 at 9:44 am
I've never seen that before. So ?currentdb.Name in the immediate window does not return the correct path?
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
June 24, 2011 at 5:46 pm
clarmatt73 (6/24/2011)
When i run the code the CurrentDb is still set to the old path, is there a way to update the database so that the VBA recognises the new location?
Your code is still pointing to the original path.
Can you provide your VBA Code?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 29, 2011 at 7:55 am
This is the line where the error is occuring
Set rstFilePath = CurrentDb.OpenRecordset("Source_File_Path")
June 29, 2011 at 8:24 am
clarmatt73 (6/29/2011)
This is the line where the error is occuringSet rstFilePath = CurrentDb.OpenRecordset("Source_File_Path")
The ("Source_File_Path") should be the path of the Database that you currently have open.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 29, 2011 at 9:14 am
What does the error say?
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
June 29, 2011 at 9:33 am
If you add a breakpoint on the Line of Code and place the mouse over the "Source_File_Path" it should display the path of the Access Database that you opened.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 30, 2011 at 3:32 am
The Source_File_Path is either a variable or a constant value somewhere in your code. Have you made a search for Source_File_Path in your VBA project already?
July 1, 2011 at 6:38 am
The first argument for the OpenRecordset method of a DAO.Database object (which CurrentDb is) can only be the name of a data source (litteral string or variable):
1. The name of a table (local or linked) (ex. CurrentDb.OpenRecordset("MyTable").
2. The name of a query (ex. CurrentDb.OpenRecordset("Query_1").
3. A SQL statement that returns a rowset (ex. CurrentDb.OpenRecordset("Select * From MyTable").
The full name (path+name+extension) of the file for a DAO.Database object is contained in the Name property of this object (ex. Debug.Print CurrentDb.Name).
You can also retrieve the information using several properties of the CurrentProject object:
1. CurrentProject.FullName returns the same info as CurrentDb.Name (ex. C:\Documents and Settings\SinnDHo\Access\SalesProject\Sales.mdb).
2. CurrentProject.Name returns the name+extension (ex. Sales.mdb).
3. CurrentProject.Path returns the full path (ex. C:\Documents and Settings\SinnDHo\Access\SalesProject) without the trailing backslash.
In your case, this argument obviously is a litteral that can only be the name of a table or the name of a query. Anything else would cause a run-time error (Run-time error 3078 'Cannot find the source table or query).
Have a nice day!
July 2, 2011 at 2:31 pm
Have you tried anything that was suggested?
It has been a week already since you opened your post. I hope that you do not have any time constraints.
I would suggest that you try some of the suggestions that were offered to you.
Search for the Constant or Variable that contains the old path.
Also search for the old path.
Then move on to the next task. Something some simple is usually resolved in a very short period of time with minimal effort.
We can't see everything that you can and we are reliant upon what you tell us.
Good luck.:-)
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 5, 2011 at 6:15 am
Hi Guys,
Thank you for your responses, apaologies for the delay in getting back to you, i have been away for a week or so and haven't had chance to log in.
I managed to find the cause of the problem, although the person who had set up the Module had used CurrentDb they had actually added the Source File Path to a table that overode it. I have updated the path in the table and the Module is now running fine.
Thnaks again for your help.
July 5, 2011 at 7:27 am
Thats great that you solved it.
Adding break points, using the Immediate, Locals & Watch Windows and stepping through you code make it easier to debug your code. 🙂
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply