CurrentDb looking at old path

  • 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

  • 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.

  • 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/

  • This is the line where the error is occuring

    Set rstFilePath = CurrentDb.OpenRecordset("Source_File_Path")

  • clarmatt73 (6/29/2011)


    This is the line where the error is occuring

    Set 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/

  • What does the error say?

    ______________________________________________________________________________
    How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.

  • 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/

  • 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?

  • 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!

  • 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/

  • 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.

  • 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