Splitting tables

  • Thread starter Thread starter Luciano
  • Start date Start date
L

Luciano

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?
 
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?
Click to expand...

Define "won't work". What error message do you get?
 
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.
 
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 solved my problem (I indeed had to open the linked table, not the current
db). Thank you very much for your excellent advise.

Luciano

Chris O'C via AccessMonster.com said:
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

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?
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
Click to expand...
[quoted text clipped - 37 lines]
Define "won't work". What error message do you get?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top