can't replicate database

[Guest]

I'm trying to replicate my database. I follow: Tools > Replication > Create
Replica. I'm prompted to create a backup. During the backup process I receive
a "Can't Replicate Database - Table tblProfiles has too many indexes."

I don't understand this. tblProfiles has 32 indexes which is the maximum
allowable. How can I possibly have too many?

Thanks!

 

[Douglas J Steele]

Access can create indexes on its own that don't show up in the list through
Table Design. For example, if you create a relationship, Access will index
the fields involved, even if you already have an index defined.

Try going through the Indexes collection for the table to see what you've
really got:

Sub ListIndexes(TableName As String)
Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim idxCurr As DAO.Index
Dim fldCurr As DAO.Field

Set dbCurr = CurrentDb()
Set tdfCurr = dbCurr.TableDefs(TableName)
For Each idxCurr In tdfCurr.Indexes
Debug.Print idxCurr.Name
For Each fldCurr In idxCurr.Fields
Debug.Print " " & fldCurr.Name
Next fldCurr
Next idxCurr

Set tdfCurr = Nothing
Set dbCurr = Nothing

End Sub

 

[Guest]

Thanks, Doug. This makes sense, however, I'm not familiar with the Indexes
collection. I can't seem to find anything in help, either.

I'm a little lost. Where/how do I use the code you provided?

Thanks!

 

[Douglas J. Steele]

Just copy it into a module, then, in the Immediate Window, type

ListIndexes "NameOfTable"

The indexes will be printed below it.

 

[Guest]

Hi, Doug.

I've tried this but I'm getting a compile error of user defined type not
defined. I've placed >< around the code that the debugger points to:

Sub ListIndexes(tblProfiles)
Dim >dbCurr As DAO.Database<
Dim tdfCurr As DAO.TableDef
Dim idxCurr As DAO.Index
Dim fldCurr As DAO.Field

Set dbCurr = CurrentDb()
Set tdfCurr = dbCurr.TableDefs(tblProfiles)
For Each idxCurr In tdfCurr.Indexes
Debug.Print idxCurr.Name
For Each fldCurr In idxCurr.Fields
Debug.Print " " & fldCurr.Name
Next fldCurr
Next idxCurr

Set tdfCurr = Nothing
Set dbCurr = Nothing

Not sure how to correct this. Any direction you have is appreciated - thanks!

 

[Douglas J. Steele]

You must be using Access 2000 or 2002. Database is a DAO object. By default,
Access 2000 and 2002 use ADO.

With any code module open, select Tools | References from the menu bar,
scroll through the list of available references until you find the one for
Microsoft DAO 3.6 Object Library, and select it.

 

[Guest]

That was the issue. I'm using 2002. The code returns:

ListIndexes "tblProfiles"
PrimaryKey
txtProfileID

This seems OK to me. Am I maybe missing something?

 

[Douglas J. Steele]

Looks okay to me as well.

What's the exact error message you're getting? I did a search using the
exact phrase Can't Replicate Database at
http://search.microsoft.com/search/search.aspx?view=msdn&st=a&c=0&s=1 and
got no hits.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

That was the issue. I'm using 2002. The code returns:

ListIndexes "tblProfiles"
PrimaryKey
txtProfileID

This seems OK to me. Am I maybe missing something?

 

[Guest]

Sorry for the delayed response, Doug. My computer took a dive and I'm in the
middle of getting it repaired.

The exact error message occurs during the conversion to design master:
Microsoft Access can't convert the database to a Design Master.

Cannot make tblProfiles table replicable - too many indexes.

Clicking on help returns:
Cannot make the <name> table replicable - too many indexes. (Error 3706)
The table cannot be made replicable because replication system indexes
cannot be added. Adding these indexes would exceed the maximum index limit.

I guess the replication process wants to create another index to that table
and it already has 32...?

--
www.Marzetti.com

Looks okay to me as well.

What's the exact error message you're getting? I did a search using the
exact phrase Can't Replicate Database at
http://search.microsoft.com/search/search.aspx?view=msdn&st=a&c=0&s=1 and
got no hits.

 

[Douglas J. Steele]

I thought that when you ran the VBA code I gave you, it indicated that there
was only one query though: that's what I'm having trouble understanding.

There's actually a newsgroup specifically for replication
(microsoft.public.access.replication). I don't know, however, whether it's
accessible through the web interface to these newsgroups, as I never use the
web interface.

 

[Guest]

Thanks for all your help, Doug!