I am exporting a large query to a delimited text file. I'm finding that it takes more than 5 minutes just to get the Export Text Wizard to load, and I'm guessing that's because Access is running the query as it loads the wizard.
View RepliesI have a (quite) specific question but I thing it covers something I simply cannot answer.
I have three UPDATE queries running on linked tables in Microsoft Access (2000/XP).
My main data table (the one to be updated) has almost 1million records
My three information tables ALL have primary keys (which are used to link the main table) and vary in size
I have atatched the three UPDATE queries plus descriptions of the field names used.
TableRecordsTime
Main DataTable900000
Mask nomenk1302 hours
Mask media90015 minutes
Mask brand4000?????
Query A
UPDATE [Main DataTable] AS z
INNER JOIN [Mask nomenk] AS n ON (z.nomCode1 = mn.nomCode1) AND (z.nomCode2 = mn.nomCode2) AND (z.nomCode3 = mn.nomCode3) AND (z.nomCode4 = mn.nomCode4)
SET z.NomenkMask1 = n!NomenkMask1;
Query B
UPDATE [Main DataTable] AS z
INNER JOIN [Mask media] AS mm ON (z.couCode = mm.couCode) AND (z.nomCode1 = mm.nomCode1) AND (z.pubCode = mm.pubCode)
SET z.MediaMask1 = mm!MediaMask1;
Query C
UPDATE [Main DataTable] AS z
INNER JOIN [Mask brand] AS mb ON (z.couCode = mb.couCode) AND (z.nomCode1 = mb.nomCode1) AND (z.brCode1 = mb.brCode1) AND (z.brCode2 = mb.brCode2)
SET z.BrandMask1 = mb!BrandMask1;
FieldnameFieldType
couCodeText
pubCodeText
nomCode1Long Integer
nomCode2Long Integer
nomCode3Long Integer
nomCode4Long Integer
brCode1Long Integer
brCode2Long Integer
My problem, quite simpley is the speed involved with running these queries. I know that query b) is the quickest with query a) a distant second (I could not even complete the running of query c) and killed it after 6 hours.
What I need to know is WHY is queryC soooo much slower than queryB when the only realy diference that I can see between them if the latter has an extra field to join on
Hello,
First time poster here so I hope this doesn’t sound too remedial. Here’s my situation…
I work for a large industrial company that has locations throughout the world. We have a DB that tracks product concepts and ideas and associated metrics for those ideas. The DB resides on a file server in North America (Raleigh, North Carolina to be exact). North American users have no trouble with the performance of the DB. It takes a moment to open (several seconds), but once it has opened there is virtually no lag time to add or edit records, run reports, view graphs, etc. However, users in Germany and the Netherlands encounter substantial lag time not only in opening, but also in updating and entering records, running reports, and viewing graphs. This is true even after they have waited for the DB to open.
The size of the DB is only around 2MB so I don’t think overall size is the issue.
There are probably no more than 3 or 4 users in the DB at the same time with most occasions being a single user so I don’t think we are having a multiple user issue.
The DB is self contained – no references to external data or splitting of any kind.
So my questions are:
1. Do you think the poor performance is a function of our network or of Access or the DB design?
2. If it is the network, is there anything that I can do in Access to help get around the hardware/network issues?
I have a list of 30 files I need to import into my access database. The files in the folder are named
1. Txt
2. Txt
3. Txt
4. Txt
5. Txt
6. Txt
7. Txt
8. Txt
When I did the first import I did it by doing an import text file, located the file 1.txt. The Import Text Wizard opened and I picked Fixed With so I could break out the information myself.After dividing that information I hit advanced and changed the field name to match what they should be. Next, then it ask me to save Import Steps and I said yes.
First: how I can use the saved import to import this file again using the text wizard
Second: how can I set it so it imports a numbers of files, maybe one or maybe all 30?
Third: how difficult would it be to all the file name in the first or last column?
I have a table in access database which contains a text field 'EDate' that stores Date value in format (12-Apr-2013). Now I want to run a sql query on that field. User will give an input date. The sql query needs to fetch me all the records from access database whose Edate is less than or equal to the user input date.
I am using DateValue function to convert my text filed Edate into date. My query is something like this:
select * from table_name where DateValue(EDate)<='user_input_date'
I am able to perform above task if the system language settings are 'English'. But if system language settings are different say Turkish, then the query fails.
I searched a lot on web and found that DateTime function compares test data with the system date time format and gives the result. Thus it fails with different language settings.
Hello,
Does anyone knows how can I export the export/import specifications (which file/directory) and how can I import/export the specifications between different versions of Access. Thanks!
I had posted this a couple weeks back but it didn't help much in the end, thanks to anybody who posted there anyway though.
I've used the Lookup wizard to make a lookup menu in one of my tables. The field I tell it to refer to in another table is a Text type. However, after the lookup wizard completes the type of the field which is uses the menu is changed to Number.
At this point it can be kind of whacked out and inconsistent, but what usually happens is if I select a value from the lookup field it refers to the auto number that I used as a primary key for the looked up value.
I created a new table, and tried to do a lookup field for for the same fields and got the same problem - I think it's an issue with the field I'm looking up.
I'm 100% sure I didn't do anything stupid like select the ID auto number for the lookup... So what's going on?
I am having some problem regarding the way the options in a lookup field is displayed. Currently the options are displayed numerically instead of displaying the actual text.
I have three tables which are ContactPersonnelT, OrganizationT and TrainingDonorT.
One of the fields in the ContactPersonnelT table is, Affiliation. This field is a lookup value (look up wizard) which comes from the OrganizationT table field, OrganizationName.
In the TrainingDonorT table I have a field called, ContactPersonnel. This field is a lookup value which comes from the ContactPersonnelT table field, ContactPersonName. In the drop down list of the ContactPersonnel, besides the ContactPersonName. I also want to display the Affiliation field. However, instead of displaying the text in the OrganizationName field, a numerical value is showing.
How to change the display to text instead of the numerical value.
I have 2 queries to check if there is any "double quote" character in any of 12 month columns in a month table of 125K records. I use 2 queries since the maximun criteria in a query is only 8. So I use 8 criteria in the 1st query then use 4 criteria to check the remaining months in the 2nd query. The month table is refreshed and created every month. The 12 month columns are changing from month to month since they are -13 month to -1 month when the month table is created. For example, if the month table is refreshed and created in May 2013, then the 12 month columns are "May 2012", "Jun 2012" .... and "Apr 2013". If the month table is created in Jun 2013, then the 12-month columns are "Jun 2012", "Aug 2012" .... and "May 2013". The end user has little knowledge of Access Databse so he seems confusing how to update the 2 queries on new month table.I am trying to see if there is a way to use 12 criteria in just one query so the end user only deals with one query's update.If there is a way to automate/improve the update of the queries/query, then it would be the best.
View 9 Replies View RelatedIs it possible to have a command button on a form to run the Text File Import Into a Table Wizard?
View 13 Replies View RelatedWill archiving older data improve response time of a database?
What is the best way to archive data knowing that older records will still be accessed twice a year?
I have an access 2003 table I want to export to a text file. I can do this but the headers disappear and don't export. I can't find anyway to make the headers export also. Can someone help please.
Thanks
Hi All,
I need to export an access table to text file automatically. It would be helpful if this could be done by some commands or a batch file etc..
I have 60 access tables. I need to export the data to one single text file (if not possible I dont have problem in exporting to 60 different text files).
This needs to be done daily. So I need a faster way. I cannot use any softwares since I dont have any budget for that. But If there is any source codes I could Look at it would be more helpful.
I am trying to export a text file from access. I know how to do it using the export wizard and it pretty much works with my VB code except 1 thing. I need the header column exported as well. There is a check box to include that in your export while using the wizard. When I save the specs and do it using the VB code everything works except it does not include the header row which I need. Is there a way to include this row without having to go through the wizard?
Here is my current code that works(except the header).
DoCmd.TransferText acExportDelim, "Amazon Spec", _
"Amazon SQL Export Query", "C:Documents and SettingspctesterMy DocumentsAmazonMorningside.txt"
The field type is "memo", the value is as following:
MELTING POINT: 166 C +/- 3 DEGREES C
MELT POINT SOFT PEARL TPU, T3080A BASE: 140 DEGREES C +/- 5
TPU-M9025 HARDNESS: 73-78 SHORE A MS5000C MELT POINT: 123 +/-5
TPU-M9025 MELTING POINT: 135 +/- 5 DEGREES C
8710 TPU T3080A MELT POINT: 144 +/- 5 DEGREES, NOT FOR BONDING
0.7MM FS-8088 MELT POINT: 138 DEGREES C +/- 5 DEGREES
After exported to Excel, some text (red font above) is missing, like below. If I copy to Excel, the same error.
MELTING POINT: 166 C +/- 3 DEGREES C
MELT POINT SOFT PEARL TPU, T3080A BASE: 140 DEGREES C +/- 5
TPU-M9025 HARDNESS: 73-78 SHORE A MS5000C MELT POINT: 123 +/-5
TPU-M9025 MELTING POINT: 135 +/- 5 DEGREES C
8710 TPU T3080A MELT POINT: 144 +/- 5 DE
How to fix this problem? Thanks!
I have 30+ queries (or table columns) I wish to export to txtfile files that I need to be able to replace each time the table and / or queries are updated.
This is the code I've used but when I run the macro the error message is that the FreeFile is not a valid outside procedure. The code debugs without issues up to but not including the macro.
Option Compare Database
Dim fFile As Long
Dim strFile As String
Dim strString As String
Dim Rs As Recordset
Dim RsSql As String
[code]....
I would like to export each record of an access table to a separate text file and use one field of each record as the source for the file name of each exported text file.
Does anyone know a way of dong this?
Hi members,
I have two empty columns in my access database whose total width is supposed to be 385 chars (255+130). I am trying to find a way to be able to export this blank field with a total of 385 blank spaces onto an ASCII text file.
A sample row looks like this:
L ***** 385spaces needed here ***** 888888 EXAMPLE DEALER NAME 1234 EXAMPLE LANE EXAMPLE ADDRESS LINE 2 EXAMPLE CITY CA88888 80088812348008881234FORD JOE DEALER NAME 8008881234
After the first letter L in position 1, I need to have 385 blank spaces and then at 387th place I have some other data to follow.
I tried a lot to use the export wizard, but it doesn't help. Is there a way to do this?
I would really appreciate any help.
Thanks,
Anyone have any idea how i would be able to export a single cells content (from a query) into a txt document without headers or extras?
What I'm essentially trying to do is view the cells content (which is in html) in a web-browser..
Any Ideas? Suggestions?
Thanks!
Hi I created an access with a picture I copied from the internet. When I run the report in Access, I can see the picture but when I export the report to a text file, the picture is lost. Is there anyway I can have the picture everytime I export the report to word format ?
View 3 Replies View RelatedI currently have a query pulling data from a database - I need to now export the data to a text file to import it into a different database. I need the format to be like below. Wondering how I can tell the query to go to the second line and then the third line like below.
*,9215146,BUILDERS LLC,4285 NICOLET,DALLAS,TX,75201
P,2
C,2,,BRD
For some reason, whenever I export a query to text, every number has trailing zeroes after it. This happens with any number, even integers.
Examples:
1.000000000
5.250000000
0.600000000
Nine decimal places would be fine if the number extended that far, but I want to get rid of the trailing zeros so that 1.0000000 becomes 1, and 5.2500000 becomes 5.25, etc.
Now, I have noticed that a setting in the Control Panel > Region and Language > Formats Tab > Additional Settings > Numbers Tab controls the numbers of decimal places, but if I adjust it to two or zero it just cuts the data off at that point.
I had to delete a database so I exported selected records using export as a text file thinking I would get some sort of delimited text file that I could later use. Instead I got some sort of fixed length file with .............. between records and | between fields.
I'd like to convert this data to a tab delimited file.
How would I do this?
Thanks
I am trying to export a text file to be imported in to excel but whenever I export as fixed width all by number columns get cut to 2 decimal places, does anybody know how to get around this?
Thanks
I have two tables identical with all text fields within the same database. One table I export as a csv, delimited with commas separating my fields and quotation text qualifiers. Where the fields are blank there are text qualifiers as well ("" - begining and ending quotations). This is what I need. The second table is exporting as a csv, delimited, commas separating the fields with text qualifier as ""- begining and ending quotations, EXCEPT the blank fields are not putting the text qualifier, and I need it.
First Table: *What I need*
"142358", "PK", "15132678", "", "123.45"
Second Table: *What I need to fix and do NOT want*
"142358", "PK", "15132678", ,"123.45"
I have inherited 1000 records which need to fit into new criteria selected from combo boxes and to do this individually by the forms will take forever so i want to copy and paste the data in the table, but when I do it doesn't fill in the form.
This may be a problem with the cascading script in the form as the fields fill in when I press f9 but disappear every time I open the form.Finally I eventually want to export the data as a table but displaying the text value rather than the key value.