Links/Indexes (Indices?)

[Guest]

ENVIRONMENT: Windows XP Pro, Office/Access 2003 Pro.

Situation: to keep DB size reasonable and avoid "invalid argument" errors
generated when DB approaches 2gb, some tables are kept and periodically
updated in separate DBs, then linked to other DBs that make use of them.

Problem: "user" DBs need to use the SEEK method in VBA code. Indices
created in the "back end" DB of a linked file are not available for that
purpose, and code to create an index in the "user" DB fails if run against a
linked table.

Any suggestions? Only workaround I've come up with so far is to create a
cloned local copy of the table in the user database, which largely negates
any filesize benefit of having a linked table in the first place, even if I
delete the clone once it's no longer needed.

 

[Brendan Reynolds]

Here's an example I posted recently in reply to a similiar question ...

Public Sub SetIndex()

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSourceDatabase As String

'Get location of database from Connect property of linked table ...

strSourceDatabase = CurrentDb.TableDefs("Employees2").Connect
strSourceDatabase = Mid$(strSourceDatabase, InStr(1, strSourceDatabase,
"=") + 1)
Set db = DBEngine.OpenDatabase(strSourceDatabase)
Set rst = db.OpenRecordset("Employees", dbOpenTable)
rst.Index = "PrimaryKey"

'Do something here - e.g. your Seek operation

rst.Close
db.Close

End Sub

 

[Guest]

Many thanks. That looks like it might 'get 'er done'!

 

[Pat Hartman\(MVP\)]

I wouldn't use seek or find at all. I would use a query with criteria to
limit the recordset to only the records I want to process and open a
recordset based on that query.