Split database problem ...

[Guest]

I have a database that has evolved and is currently Access 2002. Quite a few
forms, tables etc and I wanted to try splitting it since we have multiple
users over the network. I did the following steps:

- Open the database and do a compact/repair to make sure
it was clean.
- Tools --> Database Splitter --> split database
- backend saved to D:\BackEnd_Test (valid directory on local drive).
- Split says it completed successfully (click OK)
- Linked Table Manager shows all tables linked to the backend
- Database seems to run fine
- Compact/Repair (also tried this without doing the Compact/Repair).
- When I exit (or do the Compact/Repair), when the database comes
back up, I get a runtime error "3219 Invalid Operation" on the following
code in the main form:

[ begin code segment ]
Private Sub Set_UserName_GVars()
Dim msg As String
Dim greeting As String
Dim mydb As Database
Dim rec1 As DAO.Recordset
Dim overunder As Integer

Set mydb = CurrentDb
Set rec1 = mydb.OpenRecordset("ProjectInfo", dbOpenTable) <<== Fails here
[end code segment]

The question is -- what piece of the puzzle am I missing. Until I see the
runtime error, there are no error messages. I can open the backend database
with no problem and the table "ProjectInfo" exists and seems fine. I have
clearly found some trap door to drop through.

HELP !! Thanks.

 

[Ken Snell [MVP]]

dbOpenTable is an option that you can use *only* for local tables. It will
not work for linked tables. Change it to dbOpenDynaset in the code steps.

 

[Guest]

Hi.

The question is -- what piece of the puzzle am I missing.

Change the "dbOpenTable" constant to "dbOpenDynaset." Access won't let you
use a table-type Recordset on a linked table.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts, so that all
may benefit by filtering on "Answered questions" and quickly finding the
right answers to similar questions. Remember that the best answers are often
given to those who have a history of rewarding the contributors who have
taken the time to answer questions correctly.

I have a database that has evolved and is currently Access 2002. Quite a few
forms, tables etc and I wanted to try splitting it since we have multiple
users over the network. I did the following steps:

- Open the database and do a compact/repair to make sure
it was clean.
- Tools --> Database Splitter --> split database
- backend saved to D:\BackEnd_Test (valid directory on local drive).
- Split says it completed successfully (click OK)
- Linked Table Manager shows all tables linked to the backend
- Database seems to run fine
- Compact/Repair (also tried this without doing the Compact/Repair).
- When I exit (or do the Compact/Repair), when the database comes
back up, I get a runtime error "3219 Invalid Operation" on the following
code in the main form:

[ begin code segment ]
Private Sub Set_UserName_GVars()
Dim msg As String
Dim greeting As String
Dim mydb As Database
Dim rec1 As DAO.Recordset
Dim overunder As Integer

Set mydb = CurrentDb
Set rec1 = mydb.OpenRecordset("ProjectInfo", dbOpenTable) <<== Fails here
[end code segment]

The question is -- what piece of the puzzle am I missing. Until I see the
runtime error, there are no error messages. I can open the backend database
with no problem and the table "ProjectInfo" exists and seems fine. I have
clearly found some trap door to drop through.

HELP !! Thanks.

 

[Marshall Barton]

I have a database that has evolved and is currently Access 2002. Quite a few
forms, tables etc and I wanted to try splitting it since we have multiple
users over the network. I did the following steps:

- Open the database and do a compact/repair to make sure
it was clean.
- Tools --> Database Splitter --> split database
- backend saved to D:\BackEnd_Test (valid directory on local drive).
- Split says it completed successfully (click OK)
- Linked Table Manager shows all tables linked to the backend
- Database seems to run fine
- Compact/Repair (also tried this without doing the Compact/Repair).
- When I exit (or do the Compact/Repair), when the database comes
back up, I get a runtime error "3219 Invalid Operation" on the following
code in the main form:

[ begin code segment ]
Private Sub Set_UserName_GVars()
Dim msg As String
Dim greeting As String
Dim mydb As Database
Dim rec1 As DAO.Recordset
Dim overunder As Integer

Set mydb = CurrentDb
Set rec1 = mydb.OpenRecordset("ProjectInfo", dbOpenTable) <<== Fails here
[end code segment]

The question is -- what piece of the puzzle am I missing. Until I see the
runtime error, there are no error messages. I can open the backend database
with no problem and the table "ProjectInfo" exists and seems fine. I have
clearly found some trap door to drop through.

Right, that's because you can not use dbOpenTable on a
linked table. Change it to dbOpenDynaset. This should not
make a difference to the rest of your application unless you
do some things that rely on the properties of a table type
recordset such as using the Seek method or expecting the
RecordCount property to be accurate without doing a
MoveLast.