I uploaded quite a bit of data from an extensive list of Word Documents. In some of the Memo fields, it imported these weird Squares. Some times at Tabs or Paragraph returns.Is there an Easy way to remove these things?
View RepliesI copy some information from a website and then paste/values into Excel. I noticed that some non-printing characters are copied to. Suppose I were to import this Excel spreadsheet into an Access table. Now I need to clean up/remove those non-printing characters. What is the best way to do this in Access?
I suppose I could set up a calculated column with =SUBSTITUTE([InvHours],char(160),"") as the formula, where InvHours is the field name containing the non-printing characters. But if I did this, then I would still retain the original field with the non-printing characters which would increase the size of the database. I don't think I could delete it because then the calculated column wouldn't work. So I would rather not pursue this option if possible.
Would it be possible to somehow run a macro that could automatically be triggered as soon as data is imported, and that macro would clip out the non-printing characters without having to copy anything to blank columns?
Or does Access have some built-in feature that could do this?
I am currently using Microsoft Access 2010 32bit, and have one database acting as parent, with a second linked database as a client for people to work with, and the parent database has tables imported from Sage V21 via ODBC. I have used the following code as specified in other examples as follows:
Code:
DoCmd.TransferDatabase acImport, "ODBC Database", "ODBC;DSN=MyDNSMachineName;UID=MyUID;PWD=MyPassword;LANGUAGE=us_english;" & "DATABASE=pubs", acTable, "INVOICE", "INVOICE"
one of the tables has 10k records in it, it only transfers 77 records. After manually attempting an import via the ODBC wizard it finally lets me access all 10k records.I wish to have a single button click delete and import fresh tables without worrying if all the data is coming across.Also, when manually adding a table, I am usually asked by the wizard to specify an index, but with the code above, I am not offered that option and the tables come across with no index. I am led to believe that having tables that link to each other without an index is bad, so how do I ensure an index is created?
Hi,
Does anyone know how I can get the imported table PATHS in VB code and current database path?
Thanx :confused:
I am using Access 2002.I am going to be producing a macro that imports a txt file. Once the text file is imported there will be a empty field for a date value. Nothing in the file that is being imported will have a date in it.
I would like to (if possible) to, once the file is in the table, open an input box asking the user for a date (formated as YYYY-MM-DD) and once the date has been entered and the ok button pressed it inputs that date in to all records in the date field.
I have a table with several columns (13).
Every week i want to import a csv-file (250 records) which may or may not have been updated, say 200 are the same as previous week and 50 records are new/updated.
Is there a way to import the csv and add an extra column with who imported all of the new records (based on currentuser)?
I have a table, which contains many fields, around 90. Out of these 90 Fields, around 40 will be imported from an Excel sheet, which has same headers as the 40 field names in my table, in which they have to get copied.
So when I import data, it copies them as new records, instead what I am looking at is, the records in Excel and in Access table has a Unique Key, so when I import data, matching with this unique key, the fields should be overwritten into those records from Excel, instead of adding new records.
E.g:
Say I have 2 records in my access:
ID(auto generated) Fab_name Issue_ID Location Remarks Data_1 Data_2 Data_3
1 Fab1 193 NY Bug OC DC MC
2 Fab2 641 DRS Error AC KC FC
Now when I am importing data from Excel with the following records:
Fab_name Issue_ID Location Remarks
Fab1 193 NY - GRM Solved
Fab2 641 DRS - SGP Forwarded
So when I import the above data into my Access from Excel, based on Issue ID, it should update the values of different fields that are imported from Excel, leaving other fields (data_1, Data_2, Data_3) as it is.
And I need this to be written as a macro, so that user just has to choose the file, which he has to import.
I am taking a filing index of City Council Resolutions which is currently based in Excel and transferring the index to an Access database I am building. An issue I am having is that the Resolutions being indexed have a subject description that ranges from about 100 characters to +700. The new Access database must hold the complete contents of the Excel spreadsheet.
The direction I have taken is to divide up the subject field into three text fields as needed ([Subject1], [Subject2], [Subject3]) each with up to 255 characters. In reports I will simply combine these fields into one large text box so the user will see them as one large subject description. Memo fields are not an option because in the near future the database backend will be moved to SQL and I understand that SQL does not always play well with memo fields.
if it is possible to store text in a table that includes subscript / superscript characters. As an example; need to indicate the units of measurement for some data and therefore need to be able to pull data from the table such as the following: kg/m2, m/s2 etc. In both these case i need the 2 character to be superscript.
I'm trying to make this an automated process so pulling it directly from the database.o a method of storing the data as a text string would be ideal.Otherwise i imagine a rather difficult VBA function will be involved
We are in the process of converting from dBase to Access. We have successfully imported one of our data files into Access. When we go in and try to change the field sizes we keep getting an error that there is not enough disk space or memory. Is there a general rule when doing this? We have tried decreasing the amount of records and continue to get the error. When we originally imported the file we did not set a field size limitation so all the text fields in the structure came in at a size of 255. We are trying to change them to their proper size.
If a database is 20MB how much memory or space is required to make changes such as this? The original file contains over 300,000 records.
Any help or direction would be greatly appreciated.
Heather
I have a vba code that builds a SQL String, opens a connection to SQL server and imports the contents of a SQL View into an Access table. Everything works fine, except one column "primary_account" where I store 1 and 0 for True and False. In Access 1 shows up as -1.
View 2 Replies View RelatedI have impotred a table of my MP3 music collection from Excel in to Access.
The Fields it contains are: "Artist" - "Album" - "Track Name" - "Track No." - & "Duration"
I would like an option to be able to list only the Artist & Albums - However, when I try I end up with a list of duplicate values for the Album field (as these relate to track 1, track 2, track , etc...)
I'm sure there's a simple solution..
Can anyone please help....
Thanks,
Alun Kane
Hi,
I have a table in Excel, and when I import the table in Access, some records from a specific field shows #Num! after importing, even before I imported the table, I selected the entire field in Excel and I Formated all cells to text.
The specific field hosts records in digit format[34222256], and sometime in mixed letter-digit format[M22228778], but are supposed to be as TEXT! After importing I see the first as #Num!, and the second as M22228778.
How can I get around this issue in Excel or Access.
Thank you!
Hi, i have a fox pro memo field that has bill of materials information in like so:EACH KIT COMPRISES
1 CR10070SW £29.60 HALLITE PRODUCT
1 RBS24690 £0.82
1 RBS354 £0.95
2 RBS345 £0.75
1 S95MMEXT £3.90
£36.77 PER KIT
I need to take out the carriage returns but keep the items delimited in some way so they all apear on the one line. These are the child parts for kits so i need to keep them on one line as i import them or via a query so i can see the parent part they belong to.
Many Thanks
I have imported a worksheet from excel.
I am trying to use query criteria to seach.
the feild I am trying to search was a custom 000-000-000 cell.
I have been trying to use like "001*" but am getting nothing back.
what should I do. Do I need to change the info in excel and start over?
thanks
I am new to Access and I think there is a simple answer to this question, but I am lost.
I have several access databases that are the same, i.e forms,tables, but have different data in the tables.
I created a query in one and was planning to import into all the others. However, after importing the query to another database it did not run, i.e I could see the field names but no data showed. The field names and tables are the same in the import database as the export one. However, neither database has relationship, joins(relationships) are created within the query.
Can anyone please tell me why this query will not run when imported, I would hate to recreate it for every database I have to work with for this project.
Thank you
My "MainMenuForm" contains a button that imports a specific csv on a specific location.Someone should do this import once per day. The problem is that there is no way for me, once the import has been done, to know which records are "new".There is no unique field that distinguishes the new records from the old ones.
I was thinking of adding like a Date() field to that csv (= the date where it was imported). Can this be done automatically? So if I import a file today, the final column would be 05/02/2015If i import a new file tomorrow, it would be 06/02/2015.
Hi
I have imported data from Outlook. The field I am interested in is the
'Subject' Field. This contains 3 effective segments,
1 employee name
2 description
3 job and sub job number
example of text in field is
M.Heywood Completed Job No.1708 8
N.Curry Completed Job No.1477
I wish to move the employee name into another field and also the job and sub
job number. I have tried the following Right() Function but it is not quite
working as I want
Right$([Subject],InStr([Subject],"Completed Job No.")-1)
For the records above the results are as follows
No.1708 8
No.1477
What I really want is to only get the text after the .
Any help please as this is my first time with this function
Thanks
Richard
Hello there,
In Table A I have a field called Pin Number.
I set an input mask for this field, Pin Number.
Now I have imported a huge number of records into Table B from Excel which also contains Pin Numbers.
I need to link all fields with Pin Numbers, so my question is:
How can I change newly imported data fields to conform to a previous input mask?
many huge thanks, :)
I have an imported table that I created a form for (containing 400+ entries). I created a subform on it for a new table that I will be entering information to tie in with the imported form.
However, when I try to create a report, the report won't acknowledge any of the information. It's all outlined in the SQL, but nothing appears.
I did discover, though, that if I create a new entry on the imported table, that information appears on the report, but nothing for the existing data that was imported.
Please help!!!!!
I am importing about 1.5 million records from a .txt file into an Access database. I have the code that splits the records and adds them to the database. However, the database has a 'specialty' table that lists certain specialty codes. The incoming record has a field that is a specialty code. Any way to search the 'specialty' table for the code that is coming in on the record to be added, and then, if the record 'code' is not found, skip it, and if it is found, add it to the database. There are about 70 codes in the 'specialty' table.
View 9 Replies View RelatedI need to import a text file into access 10, the problem is as i understand it access has a db limit of 255 columns.
The text file is delimited by | symbols. As i tried to import, it shows me to have 267 columns and all the columns are unique and needs to be used for my work.
Am trying to import data into a blank database (MS access 2013). The import operation is successful. But the data in the tables gets deleted after a while. And all I can see are blank tables in Access 2013.
View 2 Replies View RelatedI am Extracting that data into an excel document from the fillable PDF and importing into Access.... NOW. Here is what I am trying to figure out. I have to alert the field of major changes, SO I am wanting Access to compare the document I imported last with the one I imported just now. Example. I change my contact information, I need a query to find that the information I submitted last time, is not the same as what I just submitted. My issue is, I cannot get a vision for this database. I can import these documents to a table BUT I cannot compare these line items with line items of the same table...
View 3 Replies View RelatedHello, Could someone advise me how to fix this little problem i have when importing a spreadsheet. My app outputs two columns into a spreadsheet with the following US date formats.The spreadsheet it generates is called dates.xls, and i need to re-arrange all the dates in the spreadsheet to a British date format dd/mm/yyyy and re-save it as dates.xls from a click of a button on my form.I know i can use text to columns in excel to fix this problem, but i want access to do it for me, preferbly in VBA, as in the future i may be working with multiple columns. Thankyou for any suggestions.20070612 2007011220070529 2007031920070530 2007040620070530 2007040620070530 2007040620070530 2007040620070530 2007040620070518 2007040620070525 2007041520070521 2007041920070608 20070430
View 2 Replies View RelatedI am using DoCmd.TransferText to import a CSV file to a new table.
The problem is some of the fields contain quotation marks which really messes things up for me later on.
Is there some way I can go through the whole table and remove them?
Maybe a Query?
Or VBA?