Compund index Using ADO

[taccea]

I have created the index below, how would i modify
it to make an index based the fields bonus_id and then another "row_id" for
example. I believe it is called a compound index?

Your help always appreciated !

Taccea


Dim idxBonusID As Index
With tdfGHTcheckDetail
Set idxBonusID = .CreateIndex("BonusIDIndex")
With idxBonusID
.Fields.Append .CreateField("bonus_id")
End With
.Indexes.Append idxBonusID
.Indexes.Refresh
End With

 

[Dirk Goldgar]

I have created the index below, how would i modify
it to make an index based the fields bonus_id and then another
"row_id" for example. I believe it is called a compound index?

Your help always appreciated !

Taccea


Dim idxBonusID As Index
With tdfGHTcheckDetail
Set idxBonusID = .CreateIndex("BonusIDIndex")
With idxBonusID
.Fields.Append .CreateField("bonus_id")
End With
.Indexes.Append idxBonusID
.Indexes.Refresh
End With

That appears to be DAO code, not ADO. I don't believe ADO defines an
Index object, though ADOX does.

As for the specifics of your question, how about:

Dim idx As Index

' ... we assume you have already opened the
' TableDef tdfGHTcheckDetail ...

With tdfGHTcheckDetail
Set idx = .CreateIndex("ixBonusIDRowID")
With idx
.Fields.Append .CreateField("bonus_id")
.Fields.Append .CreateField("row_id")
End With
.Indexes.Append idx
.Indexes.Refresh
End With

 

[taccea]

Thanks Dirk for correcting me about not starting a new thread,
Here is the text of the new one I had written so everyone can check
for follow ups..

I was calling DAO, ADO.

Here is the code that works FINE as long as I make a copy of the table in
Access2000 :

Please notice that the only difference is that "ghtCHeckDetail"
is the table brought in via DoCmd.TransferDatabase, acImport
and "ghtCHeckDetail2" is a copy.
The very same code below does NOT run on "ghtCHeckDetail"

Thanks
Taccea

****************************************
DoCmd.CopyObject , "ghtCheckDetail2", acTable, "ghtCHeckDetail"

Dim dbsPBFirst As Database
Set dbsPBFirst = CurrentDb

Dim tdfGHTcheckDetail As TableDef
Set tdfGHTcheckDetail = dbsPBFirst!ghtCheckDetail2

Dim idxBonusID As Index
With tdfGHTcheckDetail
Set idxBonusID = .CreateIndex("ixBonus_idS_row_id")
With idxBonusID
.Fields.Append .CreateField("bonus_id")
.Fields.Append .CreateField("s_row_id")
End With
.Indexes.Append idxBonusID
.Indexes.Refresh
End With
*******************************

 

[Dirk Goldgar]

Thanks Dirk for correcting me about not starting a new thread,
Here is the text of the new one I had written so everyone can check
for follow ups..

I was calling DAO, ADO.

Here is the code that works FINE as long as I make a copy of the
table in Access2000 :

Please notice that the only difference is that "ghtCHeckDetail"
is the table brought in via DoCmd.TransferDatabase, acImport
and "ghtCHeckDetail2" is a copy.
The very same code below does NOT run on "ghtCHeckDetail"

Thanks
Taccea

****************************************
DoCmd.CopyObject , "ghtCheckDetail2", acTable, "ghtCHeckDetail"

Dim dbsPBFirst As Database
Set dbsPBFirst = CurrentDb

Dim tdfGHTcheckDetail As TableDef
Set tdfGHTcheckDetail = dbsPBFirst!ghtCheckDetail2

Dim idxBonusID As Index
With tdfGHTcheckDetail
Set idxBonusID = .CreateIndex("ixBonus_idS_row_id")
With idxBonusID
.Fields.Append .CreateField("bonus_id")
.Fields.Append .CreateField("s_row_id")
End With
.Indexes.Append idxBonusID
.Indexes.Refresh
End With
*******************************

I haven't been following this from the beginning, so let me ask a few
questions:

1. The table "ghtCHeckDetail" is one that you have imported from another
database using TransferDatabase? You actually imported it; you didn't
link it?

2. What error message and number do you get when you run the code on the
original, imported table?

4. What line of the code raises the error?

5. Does it make any difference if you close the database after importing
the table, then reopen it and try to run the code?

6. Does the imported table already have indexes? What are they named?

7. Is the database from which you copied the table an Access MDB file of
the same version as the one you are copying into?

8. Is the imported table empty, or does it contain records?

9. When you copy the table and build the index on the copy, is that copy
empty, or does it contain the same records as the original?

 

[taccea]

Hello Dirk, thanks again. I have left the original code here (between the
asterisk) for reference
and I answer your questinons below under yours.

taccea (see below) . . .

I haven't been following this from the beginning, so let me ask a few
questions:

1. The table "ghtCHeckDetail" is one that you have imported from another
database using TransferDatabase? You actually imported it; you didn't
link it?
** YES, i actualli imported NOT linked it.

2. What error message and number do you get when you run the code on the
original, imported table?
** unfortunately the module that I am working on (a big function) does not

return a specific error
it only says something failed in the set of inporting functions, but I have
narrowed down to the above explanation.

4. What line of the code raises the error?
** .Indexes.Append idxBonusID

5. Does it make any difference if you close the database after importing
the table, then reopen it and try to run the code?
** no because it all has to happen simultaneously right after the import

because I am manipulating the table further to
** create other tables.

6. Does the imported table already have indexes? What are they named? ***NO

7. Is the database from which you copied the table an Access MDB file of
the same version as the one you are copying into?
** it is a table in SQL2000 using ODBC

8. Is the imported table empty, or does it contain records?

**Contains records, actually a single record.

9. When you copy the table and build the index on the copy, is that copy
empty, or does it contain the same records as the original?

** Same record(s) as the original

Thanks for your dedication.

taccea

 

[Dirk Goldgar]

** unfortunately the module that I am working on (a big function)
does not return a specific error
it only says something failed in the set of inporting functions, but
I have narrowed down to the above explanation.

Please set a breakpoint in the code and step through it line by line.
Then, when the error is raised, use the Immediate Window to display the
values of Err.Number and Err.Description. Report what they are in a
reply to this message.

** no because it all has to happen simultaneously right after the
import because I am manipulating the table further to
** create other tables.

That's all well and good, but we're engaged in a debugging process here.
We may need to force a different order of events to isolate the problem.

Hmm, that's very interesting, and could be a factor (though I don't know
how yet).

 

[david epsom dot com dot au]

Please notice that the only difference is that "ghtCHeckDetail"

is the table brought in via DoCmd.TransferDatabase, acImport

If it is an ODBC table in SQL2000, and you use TransferDatabase
to import it, then you have imported a Link, not data.

(david)

 

[Dirk Goldgar]

If it is an ODBC table in SQL2000, and you use TransferDatabase
to import it, then you have imported a Link, not data.

That's not necessarily so, David. I tested it by importing a table of
my own from a SQL2000 database using DoCmd.TransferDatabase, and it
worked fine. I went on to test the equivalent of Taccea's code to
create a compound index, and that worked fine, too.

 

[taccea]

I am going to address item 5 at this point cause I have tested it.

Yes I am importing, not linking.
Here is the code:

DoCmd.TransferDatabase acImport, "ODBC Database", _
"ODBC;DSN=GHT;UID=SA;PWD=;LANGUAGE=us_english;" _
& "DATABASE=GHT", acTable, "check_detail", "ghtCheckDetail"

And yes, when i close and open, AND do not run this code, but just try to
index the same file ghtChechDetail, it then works.
I need to know why the ODBC session is holding it.

thanks

 

[david epsom dot com dot au]

I need to know why the ODBC session is holding it.

Have you completed the operation before starting the next one?

Put in a msgbox -msgbox("Hello")- between the actions
to see if this is the problem.

Also, TransferDatabase is an Access Command which maps
to an ADO/DAO/Jet execute action. To eliminate the
Access layer from the problem, do something like this:

sSQL = "SELECT * INTO ghtCheckDetail From " & sConnect

Codedb.Execute sSQL


(david)

 

[taccea]

I will try that.
is sConnect the DSN?

thanks

TAccea

 

[Dirk Goldgar]

I am going to address item 5 at this point cause I have tested it.

Yes I am importing, not linking.
Here is the code:

DoCmd.TransferDatabase acImport, "ODBC Database", _
"ODBC;DSN=GHT;UID=SA;PWD=;LANGUAGE=us_english;" _
& "DATABASE=GHT", acTable, "check_detail", "ghtCheckDetail"

And yes, when i close and open, AND do not run this code, but just
try to index the same file ghtChechDetail, it then works.
I need to know why the ODBC session is holding it.

I like David's suggestion of testing by calling MsgBox() between the
import and the indexing. Another think you might try is inserting the
line

DBEngine.Idle dbRefreshCache

after calling DoCmd.TransferDatabase. I don't actually know a whole lot
about what goes on internally -- David knows a lot more than I do -- but
I've seen this statement used to give Jet the time to complete an
operation before proceeding to the next.

 

[david epsom dot com dot au]

is sConnect the DSN?

sorry, I expressed that badly:

INTO ghtCheckDetail From " & "[database].

;"

is the foreign name of the table, and
[database] is an ODBC connect string

sSQL = "SELECT * INTO ghtCheckDetail From " & _
"[ODBC;DSN=GHT;UID=SA;PWD=;LANGUAGE=us_english;"DATABASE=GHT]" & _
".[check_detail]"


(david)