Replies

Instead of printing an .mdi, I ended up outputing the report to a .snp file which worked perfect. I did not realize that this output was available when I wrote the original post.
Thanks anyway for the help.

Regards,
Drew

Ahhhhh…Thank you
What’s the difference between having composite verses separate indexes? I will be running multiple SQL’s against this table and the two field (Val and FlagTw) will be used in my criteria. I would like this to be as efficient as possible.

Regards,
Drew

I don’t get an error message. When I ‘Design’ on the table that was created (T_XStatBX), the ‘Indexed’ field/property indicates ‘No’.

Thanks,
Drew

Works perfect. Thank you!

Hans,

After posting this, I had found that I could use…
CurrentDb.Execute “UPDATE TLoc_Agile_MPN SET TLoc_Agile_MPN.MPNStripped = Replace([MPNStripped],'””‘,”)”

However, your approach is much less confusing. Thank you for your help, it’s greatly appreciated!

Unfortunately, we are a small division of a large corporation. Requesting a fix is painful and takes a long time to complete, sometimes over a year. However, now that you have pointed out one of the problems with the PDM export (vblf), I will submit a formal request to have it fixed. In addition, from what I’ve read, they (the creators of our PDM system) did not setup the csv format correctly. They don’t include double-quotes around all data in the text file; only strings that include a comma. I’ve setup my import sub to work around this, however I’m thinking it needs to be included in the same fix.

I’ve created a new database located on C: and pasted the code you wrote into a module. I’ve also saved 2 PDM export files to C:, one with the manufacture table (~2,370 table records; 8 fields/rec) and one for our document ID table (~90,000 table records; 11 fields/rec). The following is the time it took to complete each.

Document ID = 11 minutes, 50 seconds
Manufactures = 32 seconds

This is still too long. Any idea why it’s running so slow?
Computer attributes:
Pentium 4, 2.40GHz
2 GB Ram
MS Window XP (SP2)

On a side note, I noticed that you were able to run this in 0.35 seconds. Currently I measure the time it takes code to run by using the now() function, subtracting the difference between the StartTime and EndTime, but only pulls out seconds. Is their a way I can measure more accurately?

Thank you for you help!
Drew

Hans,

The file I used for a test was from our manufacture table, which included approximately 2370 records. I let the code run for around 3 minutes before I stopped it. The new file was created, however only 280 records were added. Is this reasonable to expect? If so, I don’t believe that this will be feasable due to some of the files having ~200,000 table records.

Another person here had tried to open a raw file in MS Word and then saving it. Everything seemed to work the same as when we save the raw file in Wordpad. I’ll have to do a few more test, but this seem like this may work(?).

Thanks,
Drew

Hans,

Does Notepad fall into the same category as Wordpad?
Attached is a zip file which includes a raw export from our PDM system.

Thanks,
Drew

Worked perfect…Thanks

Hans,

I guess I’m trying to keep the local version of this database as small and stripped as possible and I was thinking that bringing more data from the network version to the local version may not be needed for what I’m trying to accomplish at this time (???). In addition, I don’t know a lot about SQL’s and I’m trying to learn more about them to determine what I can do when running into certain situations.

As of today, I copy the network Alias table to the local database and then do the unmatched query to find all MFR’s that are not currently in the Alias table. I guess after I do the unmatched query, I could delete the local table to keep the local database as small in size as possible? I’m not sure what would be most efficent?

Regards,
Drew

Works perfect and is easier than I had expected.
Thanks

The form I am using is an Access form.
I see what I was doing wrong based on what you have indicated.
Thank you

Hans,
I can’t seem to get this to work…

Dim qdfSeq as DAO.QueryDef
Set qdfSeq = dbs.CreateQueryDef(“”, “SELECT TStudio_Layout_Bom.Seq FROM TStudio_Layout_Bom ORDER BY TStudio_Layout_Bom.Seq;”)
…
lngSeq = 0
CurrentDb.Execute “UPDATE qrySeq SET Seq = Incr([Seq])”, dbFailOnError

Do you know what am I doing wrong?
Thanks,
Drew

Wow!!! This worked perfect and the speed is unreal. Maybe it took a second, if that. Compared to ~30 seconds with the recordset object.
This is great, however I would prefer to have the query embedding within this script. Off hand, with out doing a little reading, I don’t know how to do this. Would I start by using a querydef?

Thank you very much for your help,
Drew

I still get the same error.

Is their a better or faster way to handle the resequence?
What implications exist if I ignore the error?

Thanks,
Drew