Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2011
    Posts
    27

    Unanswered: Change From CurrentProject.Connection to Path Name

    I got the following snippet from Microsoft that worked perfectly in my database until I split it. I haven't used connections like this anywhere else, so I'm at a loss. I am trying to change CurrentProject to the project whose path is beString.

    Code:
    Dim cnn As ADODB.Connection
    Dim cat As New ADOX.Catalog
    Dim col As ADOX.Column
    Dim beString As String
    
    beString = getBackEndString(strTbl)
    
    'Set connection and catalog to current database.
    Set cnn = CurrentProject.Connection
    cat.ActiveConnection = cnn
    
    Set col = cat.Tables(strTbl).Columns(strCol)
    
    col.Properties("Seed") = lngSeed
    cat.Tables(strTbl).Columns.Refresh
    I looked up what I could, but can't seem to get the syntax right. Please help!

  2. #2
    Join Date
    Jan 2011
    Posts
    27
    Figured it out. I made two errors. First, the getBackEndString was returning the currentdb.name, which gave me a "The database has been placed in a state by user 'username' on machine 'machinename' that prevents it from being opened or locked." error.

    Opening the current database as a connection will always throw the "database is in a state" error. Check the name or use another method to see if it's the current database to assign it correctly. I've seen lots of questions and one half answer, so I hope this helps other people

    Second, I saw many references to "Microsoft.ACE.OLEDB.4.0" (might be different, it had 4.0 though). Access 2007+ needs to reference 12.0 as shown below.

    Code:
    Dim cnn As adodb.Connection
    Dim cat As New ADOX.Catalog
    Dim col As ADOX.Column
    
    Dim beStr As String
    
    'Stop
    
    beStr = getBackEndString("Work")
    
    If beStr = CurrentDb.Name Then
    
        Set cnn = CurrentProject.Connection
        
    Else
    
        Set cnn = New adodb.Connection
        cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & beStr
    
    End If
    
    cat.ActiveConnection = cnn
    
    Set col = cat.Tables(strTbl).Columns(strCol)
    Now I can use this code from the front end to change the back end!

    This function in its entirety has helped me immensely while populating my database with data from other sources. The source for this code is Microsoft: AutoNumber field is not reset after you compact an Access database

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •