oh Another Importing Question

[airforceone]

This is killing me!

I have A MS Access Program that works well (I think) importing, exporting and everything…

The problem is the new branch in charge of IT solution implemented a dynamic column/fields insertion per location. Now for argument sake, lets say it will stay that way (dynamic insertion of column/fields) and we cannot do anything about it.

My query is:

Can I preselect columns/fields Excel table prior to importing (I know there is none – except importing to temp Table is still the way AFAIK)

Can I import Excel table and dynamically create new column/fields in the TARGET TABLE (MS ACCESS) based on the Excel Table

Is it more efficient if I merge excel spreadsheet (4 Sheet in 3 workbook) before importing or should I import first to TEMP TABLE in MS Access then Merge before appending to Master Table

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	MS Access (Target Table)						Excel (To Import)
2	Bno	Name	Address	BSCode	Date Entered		Bno	Name	Address	BSCode	Date Entered	Exit Date	ESCode
3	A00001	John	San Francisco	A00	2022.01.01		A00001	John	San Francisco	A00	2022.01.01	2022.02.01	A00
4	A00002	Mike	Texas	A01	2022.02.01		A00002	Mike	Texas	A01	2022.02.01	2022.03.01	A02
5	A00003	Russ	New York	A01	2022.02.01		A00003	Russ	New York	A01	2022.02.01	2022.03.01	A02
6	A00004	Peter	Las Vegas	A02	2022.03.01		A00004	Peter	Las Vegas	A02	2022.03.01	2022.04.01	A03
7	A00005	Meyer	Utah	A03	2022.04.01		A00005	Meyer	Utah	A03	2022.04.01	2022.05.01	A04
8	A00006	Austine	Camarillo	A00	2022.01.01		A00006	Austine	Camarillo	A00	2022.01.01	2022.02.01	A04
Sheet1

 

[Joe4]

I am pretty sure that you cannot automatically add new columns to an existing Access table through the ordinary/standard importing methods. I think you would need to use VBA to first identify and add those new columns to the table before you even consider importing the data. To be honest, I have never done anything like that. I usually do not try to mess with Table structure via VBA.

If you do not actually need the new data in Access, then I would probably import to a temp table, and then do an Append Query from the temp table to the final table.
Or maybe create an Excel macro that gets rid of the extra new columns before exporting/importing into Access.

If you do need to import any/all new columns into Access, this is probably going to be a problem. In well-designed Access databases, the table structures are typically pretty stable. You should seldom be changing the fields in tables. Constantly changing tables are really not conducive to a well-designed, efficient Access database.

 

[airforceone]

I am pretty sure that you cannot automatically add new columns to an existing Access table through the ordinary/standard importing methods. I think you would need to use VBA to first identify and add those new columns to the table before you even consider importing the data. To be honest, I have never done anything like that. I usually do not try to mess with Table structure via VBA.

If you do not actually need the new data in Access, then I would probably import to a temp table, and then do an Append Query from the temp table to the final table.
Or maybe create an Excel macro that gets rid of the extra new columns before exporting/importing into Access.

If you do need to import any/all new columns into Access, this is probably going to be a problem. In well-designed Access databases, the table structures are typically pretty stable. You should seldom be changing the fields in tables. Constantly changing tables are really not conducive to a well-designed, efficient Access database.

yeah I couldn't agree more! what pains me is they sometimes change the existing field name not just add new one!
I guess there's no workaround but to preselect Excel Column/Fields prior to Importing!
anyway thanks mate, it's just I'm trying to shoot my foot here

 

[airforceone]

wait mate just one more crazy idea, is there a way to present column/fields in Access?
what I mean is opening table and then selecting fields on the fly for use?
lets say I have a Car Table opening that Table then I'll preselect (by way of check box) needed fields? well just asking...

 

[Joe4]

wait mate just one more crazy idea, is there a way to present column/fields in Access?
what I mean is opening table and then selecting fields on the fly for use?
lets say I have a Car Table opening that Table then I'll preselect (by way of check box) needed fields? well just asking...

I have never heard of such a thing.

 

[welshgasman]

You could make a multi select listbox that has the Field List as it's source Type?
Then build your SQL accordingly.
If you want to add/amend fields in the DB and they are NOT in the FE, then you will need to do that in the BE.

Lost of work to get correct TBH.

 

[airforceone]

I have never heard of such a thing.

 

[airforceone]

You could make a multi select listbox that has the Field List as it's source Type?
Then build your SQL accordingly.
If you want to add/amend fields in the DB and they are NOT in the FE, then you will need to do that in the BE.

Lost of work to get correct TBH.

hmm interesting, I could try that out
Load the column/fields in a listbox and multiselect fields that will be imported to MASTER DB! sounds fun I just hope I can do it! ciao