Question Table names change when exporting to excel. (1 Viewer)

[lws]

When exporting my tables from access to excel my table names change if they have a space in the name. Example table name "New Record" turns into "New_Record".

What I am trying to do is export the table data to excel than update my access program than imort the table data back. This way I can take a vertion of my access program and update/modify it as time permits than reinsert all current data with min down time.

The code I am using is as follows:

Dim td As DAO.TableDef, db As DAO.Database
Dim out_file As String
out_file = CurrentProject.Path & "\excel_out"
Set db = CurrentDb()
For Each td In db.TableDefs
If Left(td.Name, 4) = "MSys" Then

Else

MsgBox td.Name
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
td.Name, out_file, True, Replace(td.Name, "dbo_", "")

End If
Next


Thanks for any help

 

[CJ_London]

Not quite sure what you are asking 0 why not just take a copy of the the access file and work on that instead?

With regards spaces in table and field names it is not generally a good idea - better no spaces or use an underscore.

Your routine also does not seem to have the file type extension - I think Excel9 is .xls but you need to include it in your file name

And , Replace(td.Name, "dbo_", "") is not used in export

 

[lws]

This access program is ever evolving, d/t The US EPA regulations changing every time the wind blows in a different direction. Administrating this access program is not my only responsibility. It may take several days to a week to make the changes we need. In the meantime, there is other people adding and editing records. To keep track of what has been added/changed would be a nightmare.

So when a change needs to be done, I take a copy of the program, do my changes. (However long that takes) insert the new program into place, transfer the (up to date) data tables into the new program.

The Table names are what they are. To late now.

Any help on exporting the tables over to excel with out the names of the tables changing would be greatly appreciated......

 

[lws]

With changing the acSpreadsheetTypeExcel9 to acSpreadsheetTypeExcel12 and not adding a file extention for out_file = CurrentProject.Path & "\excel_out". The xcel file is saved as a binary file (xlsb). However still no help with the name changing problem.


Dim td As DAO.TableDef, db As DAO.Database
Dim out_file As String
out_file = CurrentProject.Path & "\excel_out"
Set db = CurrentDb()
For Each td In db.TableDefs
If Left(td.Name, 4) = "MSys" Then

Else

MsgBox td.Name
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, _
td.Name, out_file, True
End If
Next

End Sub

 

[CJ_London]

Are you using a front end/backend? Still don't understand why you can't take a copy of the db and then transfer tables from the old db to the updated ones - what am I missing?

 

[lws]

My data and program are together so no I do not have a FE/BE db.

The simple answer why I would like to do this is, is TIME. I have close to 30 tables, and that takes time to bring in the data into each one.

I want to have two control buttons on a form. One to export all tables to a single excel file. When the new program is in place the other command button to import all data into the tables from a single excel file.

And no I do not want to split this into a FE/BE db due to this program is going to be used at other sites for their data, different file name, but on the same file server.

 

[Pat Hartman]

As you can see, we are having a lot of trouble with your setup since it is non-standard. It sounds like you have a monolithic database that everyone shares. The best approach is to split the database so that each individual has a copy of the FE (forms/queries/reports/code) and there is a single copy of the BE (tables only) that all the FE's link to and share.

Now the question is, if you take the data offline to work on it, how do you merge it back in?

I don't know why the column names are changing, I have never experienced that (but then I don't use names with embedded spaces or special characters). The transfer should respect your names. Do you have names in the caption property of the columns? In some versions of Access, those are the names that are used for queries and so those names would be used for the export to Excel. In any case, it doesn't matter if the column names in the worksheet are different from those in your table because you can create an append query or an update query and manually select the column names so you match Customer Name to Customer_Name.

 

[lws]

OK so no answer on this question exists. Let’s go about it by way of another avenue. How can I change the name of the table so that it will propagate the new name to all of its dependencies?

 

[CJ_London]

So leaving your db structure aside, you want all tables in a single spreadsheet. Unfortunately transferspreadsheet won't do it.

You will need to use something like copyfromrecordset which involves creating an instance of Excel and using copyfromrecordset to copy each table to a difference range or worksheet

 

[spikepl]

If the sole purpose of using Excel is to have a temporary holding space for data, why not just copy the tables into a new db and copy back or append from there? No table name changes there.

For global changes of object names you could use v-tools (google it). For a working db, making such changes is risky, since one wrong global change can disable a working application, and recuperation can be complex.

 

[CJ_London]

When exporting my tables from access to excel my table names change if they have a space in the name. Example table name "New Record" turns into "New_Record".

If you are trying to copy to a range in excel, spaces are not allowed in rangenames so my guess is excel is changing it to an underscore.

Copying to different worksheets should solve the problem since you can have spaces in worksheet names

 

[Pat Hartman]

OK so no answer on this question exists. Let’s go about it by way of another avenue. How can I change the name of the table so that it will propagate the new name to all of its dependencies?

I gave you one possible explaination. Did you examine your table and look at the Caption property for each column. Queries also have Caption properties and can have alias'. Did you look for those? Access doesn't just randomly change column names. Something you are doing is causing it or as CJ_London suggested, it could be Excel.

@CJ_London - TransferSpreadsheet can export to different sheets in a workbook. You don't need automation code to do it.

@lws - If "time" is an issue, perhaps you would consider learning how to do it the right way rather than the clunky way. Having a single database that holds everything and that everyone passes around like a hot potato, is the "Excel" way. It is not the "Access" way.

 

[CJ_London]

@CJ_London - TransferSpreadsheet can export to different sheets in a workbook. You don't need automation code to do it.

I have my own solutions on the basis that I thought i couldn't be done, but would be interested to know how it can be

Thanks

 

[Pat Hartman]

This exports two queries to the same workbook on separate tabs:

docmd.TransferSpreadsheet acExport,acSpreadsheetTypeExcel12,"AnnualRecap_Crosstab1","C:\Data\TestExport.xls",true
docmd.TransferSpreadsheet acExport,acSpreadsheetTypeExcel12,"AnnualRecap_Crosstab1_hours","C:\Data\TestExport.xls",true

 

[CJ_London]

I'll have to give it a try - I always thought Transferspreadsheet always created a new workbook rather than appending to an existing one (if it existed)

Thanks for the example

 

[lws]

@Pat – The caption property is blank.

@spikepl – That’s pretty much what I have been doing. However it’s slow, but thanks for the suggestion.


The reason I have to do it the wrong “clunky” way is because of our file server system. You place your file/db on the file server. You than locate it and check it out. You make your edits or additions to the db than you check it back in. You cannot access another db residing on the file server from one you are manipulating, as in a FE/BE configuration. Also there are multiple sites using the copies of the program under different names. Site_1_copy, Site_2_copy, Site_3_copy so on and so forth. Each site being completely separate from the others, this is the way my powers to be want it.

I have decided to do a complete rewrite of the program. To make this work plus get rid of some abandoned features left over from the previous administrator.

Thanks for your help, but I feel that I had to do more defending of my methodology than explaining of the actual problem.

 

[Pat Hartman]

If you have SQL Server available to you, that might be a better solution for sharing the BE. Each user would then have only the FE installed on his local drive.

Another alternative is Citrix/Terminal Services.

The PTB do not understand what Access is or how it should be used. An Access application is NOT a spreadsheet and should not be used as one. You are loosing the benefits of a relational database and have been lucky so far to have not run into corruption issues with the database give the way it is being used.

Did you also look at the Caption property for each column in the query you are exporting?

If you create an alias in the query, does that chang the name of the column as Excel sees it?