473,881 Members | 1,739 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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("P atientsT")
Set ind = tdf.CreateIndex ("PrimaryKey ")
With ind
.Fields.Append .CreateField("A ccountNumber")
.Unique = False
.Primary = True
End With
tdf.Indexes.App end 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 3274
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.au...
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("P atientsT")
Set ind = tdf.CreateIndex ("PrimaryKey ")
With ind
.Fields.Append .CreateField("A ccountNumber")
.Unique = False
.Primary = True
End With
tdf.Indexes.App end 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,
SubdatasheetNam e, 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.au...
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
2569
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 column each. Don't know if this makes a difference, but my foreign keys are not explicitly defined as such in the schema and most of them are nullable.
1
5201
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 expired errors on our views, which i'm assuming is due to the volume of records in our tables. Unfortunately i'm not too experiences w/ indexing tables, so my question is: 1) how do you determine which fields to index (does it have to be a...
7
3207
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 data is dbo.S_EVT_ACT, which contains about 900,000 records. The query is automatically generated by Siebel, and therefore not really modifiable. And hints are not really possible. While looking at the exec plan, we can see that what takes so long...
6
1644
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, indexes, situations i'm getting confused, irritated, etc. SITUATION I have the following situation. We have a fact table with 1,8 million records (Testsituation) and i'm inserting 100000 records and i want to
7
1823
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 for this. OK, so there is primary key (clustered) indexes (mainly composite keys), but no other indexes on the tables. As you would expect, the performance leaves a lot to be desired. A hell of a lot. We have several million rows in a lot of the...
0
1279
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 " & TableName & " IN '" & NewDBName & "' FROM " & TableName & " WHERE 1=2;" DoCmd.RunSQL sqlString 'The WHERE 1=2 ensures that no records are copied - only the
4
2064
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 gives me the output but it is taking long to execute the query. As much I had heared I want to use some indexing or cluster indexing which might help me but I am not so familiar with these things. So if any one having some solutions to execute the...
1
5363
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 the I dont need filtering in Oracle, but only indexing. If I put file references to the filtered files in the database and index these (using FILE_DATASTORE), everything works fine. But I rather put the filtered data in the database, and index it...
2
8113
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: http://kbalertz.com/954822/install-configure-Indexing-Service-Windows-Server-computer.aspx http://kbalertz.com/820105/Application-Query-Indexing-Service-Catalog-Using-Visual-Basic.aspx I can search some files .xls .doc and others but It will not find anything in .pdf
0
9926
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9776
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11095
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
10812
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10399
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
7952
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5780
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
4597
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
3223
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.