Import excel into access with a default value (1 Viewer)

[mac.vba]

Hi,

Don't know if I'm asking a simple question. I'm stuck on a step where I want to import an excel worksheet into the msaccess like we do normally. I do not have any data inside, it's just the header I will be importing. The data will be feeded by other forms based on some selections. My requirement is the "Default value" of each field should be set to 1 as we see in the property of a table in design mode.

The data would be updated later for some fields via macro or commands, but the fields were nor touched should be set to 1 (Value). Please help.

Looking for VBA CODE ONLY.

:banghead:

 

[Frothingslosh]

Well, one way you can do it is to create a permanent 'transfer table' that you always import into, and set all the default values to 1. Then after the import, use another routine to clean the data and append it to a regular table for future use. (If you go this route, make sure the first thing your import routine does is delete all records from the transfer table.)

 

[mac.vba]

There would be hundreds of tables to import from excel to access. Hence, updating the first row with 1 is not feasible in all the tabs. Below code worked to import all the tabs, but I'm looking for a code which would let me set the "Default Value" as 1 at the same time.

OR

Please give me some idea on how to update the fields with 1 while updating the record source. E.g. I have a Query which selects some fields = the header of the already imported table. In Query, the data can be seen in vertical manner, but the headers are in the horizontal manner. The Query output (in vertical manner) is as same as the names of the header (Horizontal manner) of a particular table. Can I have a code to have the table updated with 1 where the Query does not have respective header's name in its selected list? Untouched are to be updated with 1. Touched with 2.

Scenario:

Qurty Name: QryTerm
Query Output:

Result (Query Field)
English
History
Geography
Art
Science

Table Name: TblTerm
Table to be filled as:

English Economics Maths History Geography Art Geometry Science

2 below Economics, Maths & Geometry. Rest would updated with 1 as they were not in the Query output. Can it be done using a vba code recordset?


Please advice.

 

[Frothingslosh]

It sounds as if you're stuck thinking of Access as similar to Excel. The first thing to do is stop comparing vertical to horizontal - Access doesn't work that way, despite the way datasheet view looks.

It sounds as if you're confusing field names with 'row 1'. What you treat as the 'header row' in Excel is treated as field names in Access. Even when you import, if you toggle on 'First row has headers' when you import it, the headers just get converted to field names. (If you don't toggle that option on, the headers get imported as data, and the fields are just named F1, F2, F3, etc.)

Seriously, the easiest thing to do is import into a pre-defined import table with a default set to 1. What will happen is that any imported data with a value will import that value, but any field that doesn't have a value imported will get 1 instead.

You could also just import directly, then run a query on each field updating all nulls and zero-length strings to 1.

Other than that, you might be stuck doing a cell-by cell read of the spreadsheet, cleaning the data as it's imported, but I warn you right now, it's a fair amount of coding and very, very, very slow. As in: that might be all someone does each day.

 

[Rx_]

http://www.access-programmers.co.uk/forums/showthread.php?t=248093&highlight=excel
Here is a simple example concept of moving it in a cell at a time.
It leaves out the data checking, data type checking and so on.

Here is a link to a demo at our local Access users group.
http://www.access-programmers.co.uk/forums/showthread.php?t=242461&highlight=excel

Now, if you want to find the directory of your stationary Excel workbook, evaluate it and import the data, here is a link that will give you an idea what you are up for.
http://www.access-programmers.co.uk/forums/showthread.php?t=259619&highlight=harvest

Hopefully, your Excel data is somewhat in a Template format for consistency.
Otherwise, the few minutes the Excel user spared will turn into a hundred hours of the Access programmers job.