Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2011
    Posts
    42

    Unanswered: vb code is not functioning with linked table

    I'm using vb to retrieve and update data using DAO 3.6 like :

    dim db as dao.database
    dim rst as dao.recordset
    set db=currentdb
    set rst=db.openrecordset("table1")
    rst.index="index1"
    rst.seek"=",variable
    .....
    .
    rst.update

    now I splitted the database to back end and front end
    I've got an error (operation is not supported for this type of object)

    I need the code to deal with the linked tables as if it's own tables

    Thanks

  2. #2
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    From Access help: "You cannot use the Seek method with a linked table because it is impossible to open linked tables as Recordset objects of type Table".
    Have a nice day!

  3. #3
    Join Date
    Jan 2011
    Posts
    27
    I did the same thing. I don't know much about DAO, but I assume it's similar to most other methods. Instead of treating linked tables as if they were the real ones, I just get the real ones.

    Code that needs the actual table needs to reference the back end and not the currentDb. My code is modified from here: Manage Remote Backend Access Database Programmatically With VBA Code

    This checks a table to see if it is linked or actual, and returns the path of the database with the actual table. I left in the commented code from the original.

    Code:
    Function getBackEndString(tblName) As String
    
    Dim db As Database
    Dim DbPath As Variant
    Dim pathToSend As String
    
    'get back end path of linked table
        DbPath = DLookup("Database", "MSysObjects", "Name='" & tblName & "' And Type=6")
        If IsNull(DbPath) Then
        
            pathToSend = CurrentDb.Name
            'Set Db = CurrentDb 'if local table
            
        Else
        
            'Set Db = OpenDatabase(DbPath) 'if linked table
            pathToSend = DbPath
            
            'If Err <> 0 Then
                'failed to open back end database
            '    Exit Function
            'End If
            'in case back end has different table name than front end
            tblName = DLookup("ForeignName", "MSysObjects", "Name='" & tblName & "' And Type=6")
        End If
        
        getBackEndString = pathToSend
        
    End Function
    Then I call it with a table I know will be in the underlying database or the one I want to use. The code below includes the beginning of it doing things with the back end.

    Code:
    Dim cnn As adodb.Connection
    Dim cat As New ADOX.Catalog
    Dim col As ADOX.Column
    
    Dim beStr As String
    
    'known table
    beStr = getBackEndString("Work")
    
    'check if real table is in FE or BE
    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
    
    '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
    You might need to change the Provider to 4.0 or something. It was a pain to find the 12.0 syntax that works with mine.

    The simple solution would be to put in the back end's path, but it would break if you move it or link to multiple tables. I hope this works in your case!
    Last edited by Phasma; 04-07-11 at 16:22.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    Opening the back-end as a separate database is a possibility but it reduces many advantages of split databases to nil as several users will actually directly open the back-end simultaneously.

    Doing so will bring back the problems of multi-users non-split databases and the troubles that go along with them.
    Have a nice day!

  5. #5
    Join Date
    Jan 2011
    Posts
    27
    Good point, Sinndho, I didn't think about that. Luckily my code was written for my use only and not the users.

Posting Permissions

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