Access not indexing imports 1

[KeRouAZY]

Hello, I did not see this answered in the archives.

Here is my problem.

Every monday I import text files into Access databases using a macro. I have the import specifications set to import everything in the format I need it. I also have some of the fields indexed (duplicates ok).

The problem is that the fields do not index. When I look at the resulting table in design mode they are unindexed. and I have to manually change it to "Yes (Duplicates OK)".

The database itself was originally an access97 database that was converted to 2002. The import specifications were set to index after we converted.

I have tried recreating the tables, and the import specs, and they still do not index when imported.

It happens to more than one field, table, and even database... I even tried it from another computer.

Any suggestions would be welcome. Thanks in advance.

-Bill

ps - Has anyone else ever run into this?

 

[Terpsfan]

Did you try docmd.transfertext? That is the vba way to do it.

 

[KeRouAZY]

No, I have not tried doing it through scripting, just the macros. I know I could do the VB Script way, but I did not know if it would make a difference on the indexing.

Maybe this Monday I'll make a copy of the database and run that to import one of the tables as well. just to compare the data.

-Bill

 

[KeRouAZY]

I tried it on a copy. It does not work.

It only seems to work if I import them without using a macro, code, or form.

weird. Would this be a bug? or another microsoft feature?

-Bill

 

[Hap007]

Dear Bill,

Sounds like your import is setup to create a new table on import. When setup to run like this, the existing table gets deleted along with any settings you may have made.

If this is true, what I would do is:
1) Set your field definitions as you require.
2) Write a delete query to remove all records from the target table
3) Change your import spec to import/append instead of Import/create table.
4) You may also want to set the switch 'Compact on exit' to true to keep the mdb from growing too large.

Hope This Helps,
Hap


Access Developer

http://www.fcs-software.com/fcsaccessdev.html

Access based Add-on Solutions
Access Consultants forum

http://www.tek-tips.com/threadminder.cfm?pid=1121

 

[KeRouAZY]

Thanks Hap007... some times the best answer is what is looking you in the face the entire time.

Your advice was not exactly what I was looking for, but it helped me get there. I think that it must be a bug, because if Access allows you to specify the indexing in the import specs, it should actually index them, but that is probably more of a "microsoft feature" than a bug. LOL

I even found a way to do it with less work than you said.

Here was the current "old" process
1. run "delete tables" macro
2. compact database
3. run "import tables" macro
4. go into each table and change the indexing
(long process...)

Here is what I did instead of creating queries to delete the data from the tables:

I changed my macro from:
DeleteObject - Table/Tablename
to:
RunSQL - DELETE tblName.* FROM tblName; (nifty, huh?)

Then I made a module to compact the database and called it at the end of the "delete Tables" Macro.

And I was able to leave the import query the same YAY!

Here is the new way which I tested on a dummy database and will be put to the real test this Monday...

1. run "delete tables" macro
2. run "import tables" macro

Thanks again,
-Bill