Splitting tables

I saved my database as an .mdb file and I splitted my tables successfully. 
But when I save my database as an .accdb (Access 2007) database, it wont work 
anymore. Could it be an incompatibilty with my DAO-code?

Define "won't work".  What error message do you get?

Could it be your db isn't in a trusted folder and your VBA code is disabled?

Chris
Microsoft MVP





-- 
Message posted via AccessMonster.com
 http://www.**--****.com/ 

Atfter splitting my tables in Access2007, The problem seems to occur in the 
following code:

Public ws As Workspace
Public db As Database
Public tbSaldos As Recordset
Set ws = DBEngine.Workspaces(0)
Set db = DBEngine.Workspaces(0).Databases(0)
Set tbSaldos = db.OpenRecordset("tblSaldos", DB_OPEN_TABLE)

The very moment this code is reached, my database is closed, without neither 
warning nor error message.
I think this code is still in DAO-syntax, but how to convert into ADO (if 
this is the problem); I have a huge lines of similar VBA-code.

Is tblSaldos now a linked table?  It's probably crashing because it's getting
an unhandled runtime error when it tries to open the recordset as a table
type recordset if tblSaldos is a linked table.  If that's the case either
remove the DB_OPEN_TABLE parameter and let Jet choose the default or specify
dbOpenDynaset.  You may be able to use one of two other options,
dbOpenSnapshot or dbOpenForwardOnly, depending on your situation.

There's no error message when it crashes because there's no error handler in
your procedure or the procedures that call it.  Add error handling to your
procedures and you'll usually get some clue what's gone wrong.  Not
guaranteed though.

If your code uses both the DAO and ADO libraries, you need to tell Access
which library to use for the recordset objects and any other objects where
both libraries have the same name.  Example:

    Public tbSaldos As DAO.Recordset


Chris
Microsoft MVP






-- 
Message posted via  http://www.**--****.com/ 

Indeed, tblSaldos is a linked table. But I need this DB_OPEN_TABLE  parameter 
in many cases, together with an indexed field and the .seek method.
It will be quit complicated to convert all this code, but maybe there are no 
alternatives?

You sound anxious to get started rewriting your app with ADO.  Don't you like
DAO?  A word of advice:  most things we use either library for have
equivalents, so unless there's functionality that ADO has that DAO doesn't
have, don't spend time on a rewrite on a functional db.

You can use the seek method on a linked table if you use a recordset object
that's opened on the linked table's database, not the current db.  Example:

Public Function linkedTblRS(strTableName As String) As DAO.Recordset

    On Error GoTo link_Err
    
    Dim db As Database
    Dim strDBName As String
    
    strDBName = Mid(CurrentDb.TableDefs(strTableName).Connect, 11)
    Set db = DBEngine.Workspaces(0).OpenDatabase(strDBName, False, False)
    Set linkedTblRS = db.OpenRecordset(strTableName, dbOpenTable)
    
link_Exit:    
    Set db = Nothing
    
    Exit Function
    
link_Err:    
    MsgBox Err.Number & vbCrLf & Err.Description
    Err.Clear
    Resume link_Exit
        
End Function

Call the above function when you need to use seek on a linked table.  Like
this:

Public Function seekInLinkedTbl()

    On Error GoTo seek_Err
    
    Dim rs As DAO.Recordset
    Dim varBookmark As Variant
    Dim strLookFor As String

    strLookFor = "Grapes of Wrath"
    
    Set rs = linkedTblRS("tblBooks")
    rs.Index = "PrimaryKey"
    varBookmark = rs.Bookmark
    rs.Seek "=", strLookFor
    
    If rs.NoMatch Then
        rs.Bookmark = varBookmark
        MsgBox "Not found"
    Else
        MsgBox "Found " & strLookFor
    End If
    
seek_Exit:        
    Set rs = Nothing
    
    Exit Function

seek_Err:
    MsgBox Err.Number & vbCrLf & Err.Description
    Err.Clear
    Resume seek_Exit

End Function


Chris
Microsoft MVP





-- 
Message posted via  http://www.**--****.com/ 

You solved my problem (I indeed had to open the linked table, not the current 
db). Thank you very much for your excellent advise.

Luciano