Index a table
Hi all
Can you apply an index to a table from code?
I have a table which is imported and dropped each time (because it makes the db 400mb!)
What I'd like to do is create an index on a field (or three) so that subsequent queries run faster.
At the moment, the database update can take 40mins.
Can this be done...?
Danny
Can you apply an index to a table from code?
I have a table which is imported and dropped each time (because it makes the db 400mb!)
What I'd like to do is create an index on a field (or three) so that subsequent queries run faster.
At the moment, the database update can take 40mins.
Can this be done...?
Danny
Hi Danny
why not just delete Yourtable.* instead of deleting it.
Alan
why not just delete Yourtable.* instead of deleting it.
Alan
ASKER
Hey Alan - The structure f the table may change from time to time.
It's pulled from another database that our MIS team own. I need it's data, but only to run some calculations - then it can be dumped like a dog-rough girlfriend.
Dave - That creates a unique index. How about an idex of "Yes - duplicates OK"?
It's pulled from another database that our MIS team own. I need it's data, but only to run some calculations - then it can be dumped like a dog-rough girlfriend.
Dave - That creates a unique index. How about an idex of "Yes - duplicates OK"?
Hi Danny,
Sorry error in first post, trying to suggest the concept, rather than syntactical accuracy, well thats my excuse and I'm sticking to it.
CurrentProject.Connection.
ps...
sheesh dave I'm trying to keep you busy on your question so I can answer the new ones... ":0)
Sorry error in first post, trying to suggest the concept, rather than syntactical accuracy, well thats my excuse and I'm sticking to it.
CurrentProject.Connection.
ps...
sheesh dave I'm trying to keep you busy on your question so I can answer the new ones... ":0)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Dave you are being pinged at https://www.experts-exchange.com/questions/21222848/adOpenDynamic-v-adOpenKeyset-Recordsets-'Bombing'-out.html
sounds urgent to me!!
sounds urgent to me!!
LOL!
Have you been getting into that scotch again Alan :-)
Have you been getting into that scotch again Alan :-)
ASKER
Sorry guys, I can't get this to work. I'm in Acc97 on this one...
Create UNIQUE INDEX blah blah doesn't work (unknown functions)
and
Dim dbs as Database doesn't work either
Create UNIQUE INDEX blah blah doesn't work (unknown functions)
and
Dim dbs as Database doesn't work either
>> Create UNIQUE INDEX blah blah doesn't work (unknown functions)
What version of Jet you got 4.0?
>> Dim dbs as Database doesn't work either
Tools -> refrences -> select Microsoft DAO Object Library 3.?
What version of Jet you got 4.0?
>> Dim dbs as Database doesn't work either
Tools -> refrences -> select Microsoft DAO Object Library 3.?
thought you was off to bed Dave?
Reaching for another frosty VB from http://www.vb.com.au
know any good sites Dave? <grin>
Hi danny,
Can you post what you've got mate, will try to debug it for you.
Alan
Can you post what you've got mate, will try to debug it for you.
Alan
Hi Danny
try this:
Sub someSub()
CurrentProject.Execute "CREATE INDEX SNameIndex ON table1 (SName)"
'-------------------------
End Sub
Alan
try this:
Sub someSub()
CurrentProject.Execute "CREATE INDEX SNameIndex ON table1 (SName)"
'-------------------------
End Sub
Alan
Sorry Danny,
big mistake, cant create an index using currentproject.connection.
DoCmd.RunSQL "CREATE INDEX SNameIndex ON table1 (SName)"
Alan
big mistake, cant create an index using currentproject.connection.
DoCmd.RunSQL "CREATE INDEX SNameIndex ON table1 (SName)"
Alan
DoCmd.RunSql uses DAO without setting a reference to DAO
Microsoft warns, "The Microsoft Jet database engine doesn't support the use of any DDL statements with databases produced by any other database engine. Use the DAO (Data Access Objects) Create methods instead."
Alan
Microsoft warns, "The Microsoft Jet database engine doesn't support the use of any DDL statements with databases produced by any other database engine. Use the DAO (Data Access Objects) Create methods instead."
Alan
ASKER
Guys, I'm gonna abandon this question for the minute...
I'll get the db working at a snail's pace first (it's playing funny buggers at the mo)
I'll get the db working at a snail's pace first (it's playing funny buggers at the mo)
Dave