473,373 Members | 1,207 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,373 software developers and data experts.

Indexing problem with Make Table query and Create Index code (repost)

Hi,

No-one replied to this when I sent it last week. Any bites out there today??

-----

My application allows users to create a new back end for separate
purposes. It does this by using Make Table Queries and Indexing via SQL.
For example ...

sqlString = "SELECT * INTO " & TableName & " IN '" & NewDBName & "'
FROM " & TableName & " WHERE 1=2;"
DoCmd.RunSQL sqlString
'The WHERE 1=2 ensures that no records are copied - only the structure
is created.

sqlString = "CREATE INDEX AccountNumber ON PatientsT (AccountNumber)
WITH PRIMARY"
NewDB.Execute (sqlString)

When this new back end is attached to the front end, creating a new
record is up to 10 times slower than with the original back end. With
the various procedures that the front end goes through, if the back end
is on a file server, it can take up to 10 seconds between hitting my
<New recordbutton and the cursor appearing in the first field. I can
reliably fix this problem by opening the back end manually in Access,
and deleting and recreating the primary key index using the menu option
in the table design view.

I have also tried creating the back end using the following syntax for
the indexes, viz ...

Set tdf = db.TableDefs("PatientsT")
Set ind = tdf.CreateIndex("PrimaryKey")
With ind
.Fields.Append .CreateField("AccountNumber")
.Unique = False
.Primary = True
End With
tdf.Indexes.Append ind

Both indexing methods appear to work - the primary key icon sits next to
the correct field in the table design window. But the only thing that
makes the file work properly is manually deleting and recreating the
primary key in the table design window. I've tried this with empty
tables, and tables with a small amount of data - makes no difference.
Compacting, refreshing, relinking etc make no difference.

Any ideas on what is happening?

Owen Jenkins
Sep 2 '06 #1
4 3237
Owen, it seems that Access caches a bunch of info about the table in the
link, even though that's not all exposed through DAO or other object model.

If my "relinking", you mean the common approach of setting the Connect
property of the TableDef, that might not be enough to get it to discover the
new index. Does it make a difference if you delete the link (attached
table), and recreate it?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Owen Jenkins" <oj@healthbase.com.auwrote in message
news:44***********************@news.optusnet.com.a u...
Hi,

No-one replied to this when I sent it last week. Any bites out there
today??

-----

My application allows users to create a new back end for separate
purposes. It does this by using Make Table Queries and Indexing via SQL.
For example ...

sqlString = "SELECT * INTO " & TableName & " IN '" & NewDBName & "' FROM
" & TableName & " WHERE 1=2;"
DoCmd.RunSQL sqlString
'The WHERE 1=2 ensures that no records are copied - only the structure is
created.

sqlString = "CREATE INDEX AccountNumber ON PatientsT (AccountNumber)
WITH PRIMARY"
NewDB.Execute (sqlString)

When this new back end is attached to the front end, creating a new record
is up to 10 times slower than with the original back end. With the various
procedures that the front end goes through, if the back end is on a file
server, it can take up to 10 seconds between hitting my <New record>
button and the cursor appearing in the first field. I can reliably fix
this problem by opening the back end manually in Access, and deleting and
recreating the primary key index using the menu option in the table design
view.

I have also tried creating the back end using the following syntax for the
indexes, viz ...

Set tdf = db.TableDefs("PatientsT")
Set ind = tdf.CreateIndex("PrimaryKey")
With ind
.Fields.Append .CreateField("AccountNumber")
.Unique = False
.Primary = True
End With
tdf.Indexes.Append ind

Both indexing methods appear to work - the primary key icon sits next to
the correct field in the table design window. But the only thing that
makes the file work properly is manually deleting and recreating the
primary key in the table design window. I've tried this with empty tables,
and tables with a small amount of data - makes no difference. Compacting,
refreshing, relinking etc make no difference.

Any ideas on what is happening?

Owen Jenkins

Sep 3 '06 #2
Hi Allen,

Thanks for replying.
It makes no difference how I relink - whether I delete and recreate - or
just refresh. The result is the same.
If I create the back end tables with no indexes at all, I have the same
problem. Creating a record from the front end is agonisingly slow.
However, if I open the new back end, open the PatientsT table, set an
index on the AccountNumber field, then remove it again, then save the
table, this fixes the problem. Even though the table is never once saved
with an index. And this happens even if I don't then refresh the links
to the front end. So it is not the presence of the index that matters.
There appears to be something strange in the table creation process that
causes the problem. And this is done with just one sql command ...

sqlString = "SELECT * INTO " & TableName & " IN '" & NewDBName & "'
FROM " & TableName & " WHERE 1=2;"
DoCmd.RunSQL sqlString

The problem does seem to be limited to one table though. There are 11
tables in total, each created with the above line.

Owen
Allen Browne wrote:
>Owen, it seems that Access caches a bunch of info about the table in the
link, even though that's not all exposed through DAO or other object model.

If my "relinking", you mean the common approach of setting the Connect
property of the TableDef, that might not be enough to get it to discover the
new index. Does it make a difference if you delete the link (attached
table), and recreate it?
Sep 5 '06 #3
Presumably you have eliminated the usual suspects such as Name AutoCorrect,
SubdatasheetName, and so on.

Tony Toews has a list of those:
http://www.granite.ab.ca/access/performancefaq.htm

Not sure what else to suggest.

Perhaps someone else has an idea.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Owen Jenkins" <oj@healthbase.com.auwrote in message
news:44***********************@news.optusnet.com.a u...
Hi Allen,

Thanks for replying.
It makes no difference how I relink - whether I delete and recreate - or
just refresh. The result is the same.
If I create the back end tables with no indexes at all, I have the same
problem. Creating a record from the front end is agonisingly slow.
However, if I open the new back end, open the PatientsT table, set an
index on the AccountNumber field, then remove it again, then save the
table, this fixes the problem. Even though the table is never once saved
with an index. And this happens even if I don't then refresh the links to
the front end. So it is not the presence of the index that matters. There
appears to be something strange in the table creation process that causes
the problem. And this is done with just one sql command ...

sqlString = "SELECT * INTO " & TableName & " IN '" & NewDBName & "' FROM
" & TableName & " WHERE 1=2;"
DoCmd.RunSQL sqlString

The problem does seem to be limited to one table though. There are 11
tables in total, each created with the above line.

Owen
Allen Browne wrote:
>>Owen, it seems that Access caches a bunch of info about the table in the
link, even though that's not all exposed through DAO or other object
model.

If my "relinking", you mean the common approach of setting the Connect
property of the TableDef, that might not be enough to get it to discover
the new index. Does it make a difference if you delete the link (attached
table), and recreate it?

Sep 5 '06 #4
Yes, I've done all the relevant things as you suggested. No difference.

Miraculously though, the problem has disappeared today. No explanation.
I had removed and reinstated the index to the back end table by opening
the front end. I couldn't save the change to the indexing since it was a
linked table, but this procedure is all I did before the problem seemed
to resolve.

It's all a mystery to me. I'll cross my fingers that it doesn't reappear
with another client.

Thanks for your help. I'd be glad to hear if anyone has any similar
experiences.

Owen
Sep 6 '06 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: inline_four | last post by:
If I have a table with multiple foreign keys to various other tables, what's the best way to index them: one index that contains all of those columns, or multiple indexes containing one foreign key...
1
by: Danny | last post by:
sorry for posting a question that has probably been posted hundreds of times, but i can't seem to find the answer i need...We're using a Sql Server 7.0 database and recently started getting timeout...
7
by: Daniel Roy | last post by:
Hi guys, I've been playing with this query for about a week now, and I still can't find ways to speed it up. It runs for about 5 minutes. The only table (among the 41) with significant amount of...
6
by: Hennie7863 | last post by:
Hi everybody, After days reading stuff about indexing, extents, pages, 8KB, 64 KB, IGNORE_DUP_KEY, CREATE INDEX, bla bla, lalalala, lllllll, grrrrrrr and testing with different kinds of queries,...
7
by: Ryan | last post by:
I have a bit of a problem with regards an indexing strategy. Well, basically there is no indexing strategy on a set of data I have at work. Now, I didn't create the design as I would have allowed...
0
by: Owen Jenkins | last post by:
Hi, My application allows users to create a new back end for separate purposes. It does this by using Make Table Queries and Indexing via SQL. For example ... sqlString = "SELECT * INTO " &...
4
by: Amar | last post by:
Hi All, I need to select data from a database table containing huge amount of data. Now I am storing data using one primary key and I am just using simple select statement, and this process...
1
by: Server Applications | last post by:
Hello I am trying to build a system where I can full-text index documents with UTF8 or UTF16 data using Oracle Text. I am doing the filtering in a third-party component outside the database, so...
2
by: =?Utf-8?B?SmVycnkgQw==?= | last post by:
I have a server 2008 IIS 7.0 with indexing service installed. I have created the catalog and have a test page using these posts:...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.