Forum Home
Press F1
Thread ID: 104416	2009-10-27 00:40:00	Replace space with underscore in Access fieldnames	Mike (15)	Press F1

Post ID	Timestamp	Content	User
824471	2009-10-29 18:12:00	Hi Mike, it may be possible with VBA code. Without investigating this I believe there would be a way to loop through each table and grab the column names. Determining the new name (i.e. with underscores) is a simple replace operation in the code. Changing the name of the column is achieved using the SQL Alter Table & Alter Column ... www.w3schools.com A quick look on the net indicated you cant change names in Access if theres a relationship with other tables. I dont know whether this is true or not, but if so then thats a huge stumbling block. By the time all the investigation was done and coded you could have manually amended the column names.	Parry (5696)
824472	2009-10-30 01:07:00	Hmm, an interesting challenge. Of greater immediate concern however is the impact that renaming tables and fields will have on the functions within this existing database. You may find you end up having to modify every query, form, macro and module as well in order to keep it working!No impact at all. Not so much a database than an Access MDB file with some data in it :) It all depends on how 'live' the database is - how much use it's getting and how up to date your data in the target app needs to be - whetther or not you'll be needed to repeat this process regularly to refresh the data. A bit more info would be nice. Care to name the destination app or database structure?Not live at all, its a data dump out of a trimble GPS application that I need to join with the spatial component of the same data in a GIS application (ArcGIS). The ArcGIS doesn't support spaces in the field names, so need to remove the spaces so I can create the join correctly. The joined data will then be exported into a GIS database and the original will never be looked at again (all things going well ;)) I will probably need to repeat the process though for future data dumps out of the GPS, which is why I'm hoping to find there's a quick easy way to bulk remove those spaces. Cheers, Mike.	Mike (15)
824473	2009-10-30 01:10:00	Hi Mike, it may be possible with VBA code.I suspect it can, but I don't know enough about Access to figure that one out. I could do it in Excel VBA no problem. A quick look on the net indicated you cant change names in Access if theres a relationship with other tables. I dont know whether this is true or not, but if so then thats a huge stumbling block.won't be a problem in this case :) By the time all the investigation was done and coded you could have manually amended the column names.Possibly true, however I suspect I'll have to repeat this process, so am hoping to figure a way to do it quickly each time. I think pulling the data into Excel then re-exporting to Access might be the quickest/easiest way to do it at the moment. Cheers, Mike.	Mike (15)
824474	2009-10-30 06:15:00	No impact at all. Not so much a database than an Access MDB file with some data in it :) Not live at all, its a data dump out of a trimble GPS application that I need to join with the spatial component of the same data in a GIS application (ArcGIS). The ArcGIS doesn't support spaces in the field names, so need to remove the spaces so I can create the join correctly. The joined data will then be exported into a GIS database and the original will never be looked at again (all things going well ;)) I will probably need to repeat the process though for future data dumps out of the GPS, which is why I'm hoping to find there's a quick easy way to bulk remove those spaces. Cheers, Mike. Ohh, this sounds promising - if the data you want to get to is entirely numeric (numbers or date/time only), or more specifically, NO TEXT (other than in the field names), then this is a piece of cake to deal with. The one hassle is this solution involves exporting each table seperately. (and there's probably still a better way) 1. Select a table, and select Save As/Export 2. Export as file type Text File, with the Field Names in first line option ticked. 3. Open the text file with Word. 4. Do Find/Replace - replacing " " with "_" 5. Save the resultant file, still as text format. Done. You could now attempt to load the file into your destination GPS database, or re-load (import) it into the original Access database, and the underscores you need will be in place. This will get you new tables with underscores in all field names. For next time. you can use these new table structures via a query next time to simply copy from the problematic tables directly into the 'solution' tables, without needing the export and import trick. NOTE: this trick can still be used even if the tables have text fields(and spaces), but will involve an extra step... Do 1 to 5 as above. You will now have a new table with the correct structure and field names, but 'corrupted' text data with unwanted underscores. Empty the table of all data. Open the source table with the spaces in the field names. Select All, Copy. Go to the destination table with the underscored field names. Click on the record selector on the left of the tables datasheet view. Paste. Done. (but repeat for each table). Still too much work perhaps?	Paul.Cov (425)
824475	2009-10-30 06:23:00	No offense Mike, but you could have done everything manually by now and still have had time left over..	beeswax34 (63)
824476	2009-10-30 07:27:00	No offense Mike, but you could have done everything manually by now and still have had time left over.. :) no doubt (although I haven't worked on it at all ;) ) although the main reason I want a shortcut is that I will have to do this again... and again... and again (I imagine). So its not just a one-off. Otherwise, yes, I would have done it manually. Cheers, Mike.	Mike (15)
824477	2009-10-30 07:34:00	If it's going to be a repetitive task, then it is worth building a stand-alone database to do the work for you. All it needs is the correct action queries and / or linked tables to get the data in from your source database. A relatively short macro could then do all the work for you within seconds of opening the database. Just place your source database with the same name each time and in the same location whenever you are ready to extract the data. Open your intermediary database. give it a second or so to pull in the data from the latest copy, then bingo, it is ready to provide the data to your final app. Then you only go through this bother once.	Paul.Cov (425)
1 2