Microsoft KB Archive/248966

From BetaArchive Wiki

Article ID: 248966

Article Last Modified on 1/26/2005


  • Microsoft Access 2000 Standard Edition

This article was previously published under Q248966

Advanced: Requires expert coding, interoperability, and multiuser skills.

This article applies only to a Microsoft Access project (.adp).


In a Microsoft Access project, after you change the connection to point to a different database, you see unexpected data in combo boxes, list boxes, and reports. You may also notice that the data is from the previous database that you were connected to.


An Access project actually has two connections to your database. A secondary connection is used for combo boxes, list boxes, and reports. When you switch the connection, either by clicking Connection on the File menu or programmatically, the alternate connection does not change. This causes you to see data from the previous database in combo boxes, list boxes, and reports.


To resolve this problem, obtain Microsoft Office 2000 Service Release 1/1a (SR-1/SR-1a).

To obtain SR-1/SR-1a, click the article number below to view the article in the Microsoft Knowledge Base:

245025 OFF2000: How to Obtain and Install Microsoft Office 2000 Service Release 1/1a (SR-1/SR-1a)

To temporarily work around this problem, after you change the connection, immediately close and reopen the Access project.


Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article. This problem was corrected in Microsoft Office 2000 SR-1/SR-1a.


Steps to Reproduce Behavior

These steps involve creating a new copy of the NorthwindCS sample database to demonstrate this behavior. To re-create these steps, you must have access to a computer that is running either Microsoft Data Engine (MSDE) or Microsoft SQL Server 7.0 that has a copy of the sample NorthwindCS database installed.

  1. Create a new Access database.
  2. In the Database window, click Modules, and then click New.
  3. On the Tools menu, click References.
  4. Click to select the Microsoft SQLDMO Object Library check box, and then click OK.
  5. Type the following line in the Declarations section if it is not already there:

    Option Explicit
  6. Type the following procedure:

    Function CopySQLDB(strLogin As String, strPwd As String, _
    strSrv As String, strDatabase As String, strNewName As String)
    ' Parameters
    ' --------------------------------------------------------------
    ' strLogin:  Name of the Login account used.
    ' strPwd:  The Password for the login account.
    ' strSrv:  The server to which you are connecting.
    ' strDataBase:  The name of the database you want to copy.
    ' strNewName: Desired name of the new copy of the database.
       Dim sql As Object
       Dim db As Object
       Dim strMDFfilePath As String
       Dim strMDFfileName As String
       Dim strLOGfile As String
       On Error GoTo CopySQLDB_Err
       'Create Objects
       Set sql = CreateObject("SQLDMO.SQLServer")
       Set db = CreateObject("SQLDMO.Database")
       'Connect to the server
       sql.Connect strSrv, strLogin, strPwd
       'Set the database object
       Set db = sql.Databases(strDatabase, "dbo")
       'Determine the location of database files, primary and log
       strMDFfilePath = db.PrimaryFilePath
       strMDFfileName = _
       strLOGfile = Trim(db.TransactionLog.LogFiles(1).PhysicalName)
       'Clear out the database object
       Set db = Nothing
       'Detach database to allow copying
       sql.DetachDB (strDatabase)
       'Copy database files to new names
       FileCopy strMDFfileName, strMDFfilePath & strNewName & ".mdf"
       FileCopy strLOGfile, strMDFfilePath & strNewName & "_log.ldf"
       Debug.Print "Database Created"
       'Re-attach original database
       sql.AttachDB strDatabase, strMDFfileName & "," & strLOGfile
       sql.AttachDB strNewName, strMDFfilePath & strNewName & _
       ".mdf" & "," & strMDFfilePath & strNewName & "_log.ldf"
       'Set database object to the new database
       Set db = sql.Databases(strNewName, "dbo")
       'Change data in one table for testing purposes
       db.ExecuteImmediate ("Update categories Set " & _
       "CategoryName = CategoryName + 'X'")
       'Clear out the objects before ending
       Set db = Nothing
       Set sql = Nothing
       Exit Function
       'Basic error handling.
       MsgBox Err.Description, vbInformation, "SQL OLE Automation"
       Resume CopySQLDB_End
    End Function
  7. Type the following code in the Immediate window, and then press ENTER:


    NOTE: This is an example that may work in most cases. If your server is not local, you should instead type the name of the server to which you are connecting instead of the keyword "(local)". In addition, this example presumes that sa is logging on with no password. You can also log on with another account that has similar permissions.

  8. When you see the words "Database Created" in the Immediate window, close the Access database.

    At this point a copy of the NorthwindCS SQL server database has been created with the name NwindCS. It also has made a slight change to the Categories table in NwindCS.
  9. Open the Access project NorthwindCS that is on that same server.
  10. Open the Products form, and then click the Category combo box. In the combo box, note that you see the name of the categories as expected.
  11. Close the Products form, and then on the File menu, click Connection.
  12. In the middle of the Data Link Properties dialog box, click the arrow for the box that is named Select the database on the server. Note that NwindCS appears in the list.
  13. Select NwindCS, and click OK. The Access project is now using NwindCS.
  14. Open the Categories table. Note that the Category names in this database each have an X at the end. This is a small change made by the function that you ran earlier. Close the table.
  15. Again open the Products form, and then click the Category combo box. Note that the categories listed do not have an X at the end.

    Even though the Access Project is now using the NwindCS database for tables, stored procedures, and views, it is still using NorthwindCS for combo boxes, list boxes, and reports.

    Close NorthwindCS and reopen it.
  16. Open the Products form, and then click the Category combo box. Note that the categories now have an X at the end.

The Access project is now using all the objects from the database specified in the Data Link Properties dialog box, as expected.

Additional query words: pra

Keywords: kbbug kbfix KB248966