Split database causes error 1

[FoxProProgrammer]

Hi!

I developed a database in Access 2003 SP2. The application works fine on my development computer. I used the Access Database Splitter (Tools\Database Utilities\Database Splitter) to separate the tables from the application. The tables are stored on our corporate network drive and the application will be stored on each user's computer. After I split the database, an error occurs in the Click procedure of a listbox. The code is:

Dim row As Integer

Dim db As dao.Database
Dim recset As dao.Recordset

Set db = CurrentDb
Set recset = db.OpenRecordset("tbl_temp", dbOpenTable)

The line that causes the error is:

Set recset = db.OpenRecordset("tbl_temp", dbOpenTable)

The error is:

Run-time error '3219'

Invalid operation.

I don't know if the error is related to the split of the database or something else. The only that I can think of is dbOpenTable won't work on a table stored on a remote drive. Any ideas?

Thanks!

dz

 

[KyleS]

the code you've given is trying to open "tbl_Temp" within the current dB.

Is the table linked to the front end?

If not tDAO won't know where to look for the table

Kyle

 

[FoxProProgrammer]

Hi Kyle,

Yes, all the tables are linked. Sorry, I forgot to mention that. I used the Linked Table Manager in Access to link all the tables.

Thank you!

dz

 

[KyleS]

Fox,

Try switching ...dbOpenTable)
to
...dbOpenDynamic)

My Dao knowledge isn't what it used to be (any reason you're not using ADO?)


Kyle

 

[FoxProProgrammer]

dbOpenDynamic causes the same error.

I tried dbOpenDynaset. It gets by

Set recset = db.OpenRecordset("tbl_temp", dbOpenDynaset)

But fails a few lines down.

If lst_Parts.ItemsSelected.Count = 0 Then       ' Nothing is selected.
    ClearSubFrm                                 ' Delete all records in the temporary storage.
Else                                            ' At least one item is selected.
    Me!subfrm_PartSN.Form.PartSN.Locked = False ' Unlock the serial number field to allow data entry.
    
    With recset
        .Index = "lst_row"

The .Index causes:

Run-time error '3251'

Operation is not supported for this type of object.

I believe this is because .Index is only valid for table type objects. That's why I used dbOpenTable instead of dbOpenDynaset when I wrote the code.

I used DAO over ADO for two reasons.
1. I don't have any experience writing ADO code. I have plenty of experience with DAO code.
2. My understanding is that ADO is most useful when you will be accessing data outside of the Access environment. Since all the data in this application is in Access, I saw no reason to learn ADO for this particular application.

If switching to ADO is the only way to solve this problem I'll do it, but I suspect there's another way. At least I hope that's the case!

Thanks,

dz

 

[TheAceMan1]

How are ya FoxProProgrammer . . .

[blue]I tried dbOpenDynaset. It gets by . . .[/blue]

[blue]Sets or returns a value that indicates the name of the current Index object in a [purple]table-type Recordset[/purple] object (Microsoft Jet workspaces only).[/blue]

The question is what are you attempting to do with:

[blue]    With recset
        .Index = "lst_row"[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[FoxProProgrammer]

Aceman,

The table has more than one index. The .Index funtion sets the correct index so the Seek will work. Can't I have multiple indexes?

dz

 

[KyleS]

Fox,

Very sorry about mistyping the Dynaset as Dynamic.

Anyway, why not use the .FindNext method of your new Dynaset recordset? It's actually easier than setting the .Index and then using .Seek

Kyle

 

[TheAceMan1]

[blue]Can't I have multiple indexes?[/blue]

You most certainly can! However (correct me if I'm wrong) you using [blue]dbOpenDynaset[/blue] which is not a [blue]table type[/blue] recordset!

To my knowledge access to the [blue]indexes collection[/blue] is bourn out of [blue]TableDefs[/blue]. In fact microsoft goes on to say:

[blue]The setting or return value is a String data type that evaluates to the name of an Index object [purple]in the Indexes collection of the Tabledef or table-type Recordset object's TableDef object.[/purple][/blue]

Perhaps a [blue]TableDef[/blue] will work for you?

BTW: I believe the Indexes Collection is available when you use [blue]dbOpenTable[/blue] but is read only! . . . (since its not a TableDef). I'll do some testing to double-check this . . .

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[FoxProProgrammer]

Kyle, You are absolutely correct! I eliminated .Index and .Seek and used .FindFirst. It works on the remote tables. Thanks very much!

Aceman, I used dbOpenTable so I could use .SEEK. It worked on my development computer, but the Set recset statement caused an error when I split the database and moved the tables to our network drive. I can only assume that dbOpenTable won't work on remote tables. Thanks you very much for taking time to help me.



dz

 

[KyleS]

Fox, glad we could help you!

For further clarification, the reason dbOpenTable won't work on linked tables is because it's trying to open a tabledef type of object. Tabledefs are only stored for local tables, so in this case, to open it with dbOpenTable you would first have to set you DAO.Database object to the back-end dB. To only reference the front end, you have to use the dbOpenDynaset.



Kyle

 

[FoxProProgrammer]

Kyle,

Thanks for the additional info. It makes sense...just curious about one thing that you mentioned.

to open it with dbOpenTable you would first have to set you DAO.Database object to the back-end dB.

Do you know how to do this? I am happy with the way my form works, but I might need to know how to do this in the future. The Set refers to the table name and the table is linked to the back end. Can't Access figure this out? I wouldn't want to put the absolute path to the file on the back end because it might change.

Thanks!

dz

 

[KyleS]

Fox,

The easier way would be with the absolute path, but there is another way.

Absolute path:

    Dim dbs As DAO.Database
    Dim strFilePath As String

    strFilePath = "C:\Program Files\Microsoft Office" & _
        "\Office\Samples\Northwind.mdb"

    Set dbs = DBEngine(0).OpenDatabase(strFilePath)

Option 2:

From:

http://support.microsoft.com/kb/210062

'===============================================================
' The GetLinkedDBName() function requires the name of a
' linked Microsoft Access table, in quotation marks, as an
' argument. The function returns the full path of the originating
' database if successful, or returns 0 if unsuccessful.
'===============================================================

Function GetLinkedDBName (TableName As String)
   Dim db As DAO.Database, Ret
   On Error GoTo DBNameErr
   Set db = CurrentDb()
   Ret = db.TableDefs(TableName).Connect
   GetLinkedDBName = Right(Ret, Len(Ret) - (InStr _
      (1, Ret, "DATABASE=") + 8))
   Exit Function
DBNameErr:
   GetLinkedDBName = 0
End Function

So your code would look like this:

    Dim dbs As DAO.Database
    Dim strFilePath As String

    strFilePath = GetLinkedDBName ("YourTableNAmeHere")

    Set dbs = DBEngine(0).OpenDatabase(strFilePath)

It does appear you can open the linked table as a tabledef, well, I guess I was only half right before. Tableddef = Ok, dbOpenTable = Not Ok...

Kyle