locked
Access VBA "CurrentDB()" undefined after upsize to SQL Server RRS feed

  • Question

  • I upsized an access 2007 database to SQL Server using the Access Upsize wizard. Now, CurrentDB() is undefined in all VBA code. I use this a lot in navigating the database for a number of reasons. Can anyone tell me why upsizing breaks this?
    Larry the Lost
    Tuesday, December 13, 2011 12:30 AM

Answers

  • Larry,

    CurrentDb doesn't exist in ADPs.

    Frankly, I think a lot of people here will agree with me, you can forget about ADP at all. If you have SQL Server Management Studio for editing server-side objects, feel free to use ACCDB with ODBC linked tables. Here you still have all the power of SQL Server. You can use stored procedures, table-valued functions or whatever SQL Server object you want. There pass-through queries in Access for such purposes. And the main thing - having ACCDB doesn't require to rebuild the whole application (only some little remarks in terms of Recordsets options (like dbSeeChanges) etc.) but ADP does.


    Andrey V Artemyev | Saint-Petersburg, Russia
    Russian blog artemyev.biztoolbox.ru
    English blog enblog.biztoolbox.ru
    • Marked as answer by Bruce Song Monday, December 26, 2011 4:06 AM
    Tuesday, December 13, 2011 11:15 AM
  • Upsized to ADP.

    As Andrey said, in an ADP, CurrentDb returns Nothing. That's because CurrentDb returns a reference to the database in which the code is running, and an ADP is a "project" with no data of its own.  For most purposes, you would do better to use an ACCDB or MDB file with linked tables, rather than an ADP.  Certainly if you have made extensive use of CurrentDb you will have less to change if you do it that way.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html
    • Marked as answer by Bruce Song Monday, December 26, 2011 4:06 AM
    Tuesday, December 13, 2011 1:48 PM
  • Upsized to ADP.

    Here's en example of one of the code elements. This is normally called by some code that displays the name of the current database file on a form. Have been using this construct for years...

    Function DatabaseFileString() As String

       Dim db As DAO.Database
       Set db = CurrentDb()
       DatabaseFileString = db.Name   'Code gets error here: "Run-time error '91': Object variable or With block variable not set"
                                                        ' At this point, db = Nothing due to the assignment error.

    End Function

    I'll try today to just upsize to an ACCDB with linked tables and see if that makes a difference. I was really hoping to take advantage of the power of SQL Server - server-hosted queries, etc... All of these things seem to get transferred to the SQL Server, but if I have to rebuild the Access front end by hand, it probably won't be worth it.

     


    Larry the Lost


    Aside from other poster comments.....

    If you still want that ADP to work like you intended.......try......

    Function DatabaseFileString() As String
    'check the name of your ADP database 

       DatabaseFileString = CurrentProject.Name

    End Function

    • Marked as answer by Bruce Song Monday, December 26, 2011 4:06 AM
    Wednesday, December 14, 2011 1:43 AM
  • And given that ADP forms were based on ADO instead of DAO, it may be more usefull to use ADO and CurrentProject.Connection instead of CurrentDb (which is oriented toward the use of DAO).
    • Marked as answer by Bruce Song Monday, December 26, 2011 4:06 AM
    Thursday, December 15, 2011 11:28 AM

All replies

  • We can't see why it would fail. Maybe if you could give us the code where the currentdb causes this?

    Tuesday, December 13, 2011 1:27 AM
  • I upsized an access 2007 database to SQL Server using the Access Upsize wizard. Now, CurrentDB() is undefined in all VBA code. I use this a lot in navigating the database for a number of reasons. Can anyone tell me why upsizing breaks this?


    Did you upsize to an ACCDB or MDB database with linked SQL tables, or did you upsize to an ADP?

    When you say "CurrentDb() is undefined", do you mean that it doesn't compile, or that it returns the special object value Nothing?

     


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html
    Tuesday, December 13, 2011 1:49 AM
  • Upsized to ADP.

    Here's en example of one of the code elements. This is normally called by some code that displays the name of the current database file on a form. Have been using this construct for years...

    Function DatabaseFileString() As String

       Dim db As DAO.Database
       Set db = CurrentDb()
       DatabaseFileString = db.Name   'Code gets error here: "Run-time error '91': Object variable or With block variable not set"
                                                        ' At this point, db = Nothing due to the assignment error.

    End Function

    I'll try today to just upsize to an ACCDB with linked tables and see if that makes a difference. I was really hoping to take advantage of the power of SQL Server - server-hosted queries, etc... All of these things seem to get transferred to the SQL Server, but if I have to rebuild the Access front end by hand, it probably won't be worth it.

     


    Larry the Lost
    Tuesday, December 13, 2011 9:49 AM
  • Larry,

    CurrentDb doesn't exist in ADPs.

    Frankly, I think a lot of people here will agree with me, you can forget about ADP at all. If you have SQL Server Management Studio for editing server-side objects, feel free to use ACCDB with ODBC linked tables. Here you still have all the power of SQL Server. You can use stored procedures, table-valued functions or whatever SQL Server object you want. There pass-through queries in Access for such purposes. And the main thing - having ACCDB doesn't require to rebuild the whole application (only some little remarks in terms of Recordsets options (like dbSeeChanges) etc.) but ADP does.


    Andrey V Artemyev | Saint-Petersburg, Russia
    Russian blog artemyev.biztoolbox.ru
    English blog enblog.biztoolbox.ru
    • Marked as answer by Bruce Song Monday, December 26, 2011 4:06 AM
    Tuesday, December 13, 2011 11:15 AM
  • Upsized to ADP.

    As Andrey said, in an ADP, CurrentDb returns Nothing. That's because CurrentDb returns a reference to the database in which the code is running, and an ADP is a "project" with no data of its own.  For most purposes, you would do better to use an ACCDB or MDB file with linked tables, rather than an ADP.  Certainly if you have made extensive use of CurrentDb you will have less to change if you do it that way.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html
    • Marked as answer by Bruce Song Monday, December 26, 2011 4:06 AM
    Tuesday, December 13, 2011 1:48 PM
  • Upsized to ADP.

    Here's en example of one of the code elements. This is normally called by some code that displays the name of the current database file on a form. Have been using this construct for years...

    Function DatabaseFileString() As String

       Dim db As DAO.Database
       Set db = CurrentDb()
       DatabaseFileString = db.Name   'Code gets error here: "Run-time error '91': Object variable or With block variable not set"
                                                        ' At this point, db = Nothing due to the assignment error.

    End Function

    I'll try today to just upsize to an ACCDB with linked tables and see if that makes a difference. I was really hoping to take advantage of the power of SQL Server - server-hosted queries, etc... All of these things seem to get transferred to the SQL Server, but if I have to rebuild the Access front end by hand, it probably won't be worth it.

     


    Larry the Lost


    Aside from other poster comments.....

    If you still want that ADP to work like you intended.......try......

    Function DatabaseFileString() As String
    'check the name of your ADP database 

       DatabaseFileString = CurrentProject.Name

    End Function

    • Marked as answer by Bruce Song Monday, December 26, 2011 4:06 AM
    Wednesday, December 14, 2011 1:43 AM
  • And given that ADP forms were based on ADO instead of DAO, it may be more usefull to use ADO and CurrentProject.Connection instead of CurrentDb (which is oriented toward the use of DAO).
    • Marked as answer by Bruce Song Monday, December 26, 2011 4:06 AM
    Thursday, December 15, 2011 11:28 AM