Splitting tables

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?
 
P

pietlinden

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?
 
C

Chris O'C via AccessMonster.com

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

Chris
Microsoft MVP
 
L

Luciano

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.
 
C

Chris O'C via AccessMonster.com

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
 
L

Luciano

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?
 
C

Chris O'C via AccessMonster.com

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

Luciano

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...
 
Top