Add/imply fields when importing range into ACCESS

[MikeF]

When importing a named range from Excel into Access...
- columns C thru J, rows starting at 6 but ending differently all the time -
... am looking to add 2 named 1-cell ranges from Excel - that are *not*
included in the orignal named range - to the import, ie CityID and EventID
--- to their corresponding fields in Access.

- Excel range to import: MyExcelRange
This range begins in column C in Excel. *** Does *not* contain the
following in Excel, both of which need to be imported to Access, as many rows
as required:

- Excel 1-cell range to add/imply upon import to Access - in the 1st field
to the left in Access [column B *IF* it were in Excel] : EventID

- Excel 1-cell range to add/imply upon import to Access - in the 2nd field
to the left in Access [column A *IF* it were in Excel] : CityID

Of course, this could be physically re-constructed on another tab in Excel,
but ...
a) This schema is required for multiple tabs.
b) It would be redundantly using large groups of data.
b) The workbook size would start to become prohibitive.

Any assistance would be greatly appreciated.
And please let me know if this needs clarification.
Regards,
- Mike

 

[Dick Kusleika]

When importing a named range from Excel into Access...
- columns C thru J, rows starting at 6 but ending differently all the time -
.. am looking to add 2 named 1-cell ranges from Excel - that are *not*
included in the orignal named range - to the import, ie CityID and EventID
--- to their corresponding fields in Access.

- Excel range to import: MyExcelRange
This range begins in column C in Excel. *** Does *not* contain the
following in Excel, both of which need to be imported to Access, as many rows
as required:

- Excel 1-cell range to add/imply upon import to Access - in the 1st field
to the left in Access [column B *IF* it were in Excel] : EventID

- Excel 1-cell range to add/imply upon import to Access - in the 2nd field
to the left in Access [column A *IF* it were in Excel] : CityID

Of course, this could be physically re-constructed on another tab in Excel,
but ...
a) This schema is required for multiple tabs.
b) It would be redundantly using large groups of data.
b) The workbook size would start to become prohibitive.

Any assistance would be greatly appreciated.
And please let me know if this needs clarification.
Regards,
- Mike

How are you importing it into Access?

 

[MikeF]

Dick,
Thanx for the reply.

Am merely using the Access "Get External Data", then "from Excel".
It's a straight import, not a link [although at some point in the future
will need to do that].

A few clarifying notes:
- The Access table has 6 fields, the first two being EventID and CityID.

- The Excel range has 4 fields in columns C thru F [although those fields
could be in any 4 contiguous columns]. Those fields exclude EventID and
CityID.

- EventID and CityID are both named, 1-cell ranges on another worksheet in
the same Excel workbook.

- It is imperative for the Access table that the values in these 1-cell
ranges [EventID and CityID] "hit" the import from Excel, in as many rows as
necessary.

*** ALSO NOTE - there are dozens of different workbooks containing these
same
ranges that eventually will need to be imported into the Access table.
The values for each of the three ranges are different in each workbook.

Does the above help??

How are you importing it into Access?
--
Dick Kusleika
Microsoft MVP-Excel
http://www.dailydoseofexcel.com

When importing a named range from Excel into Access...
- columns C thru J, rows starting at 6 but ending differently all the time -
.. am looking to add 2 named 1-cell ranges from Excel - that are *not*
included in the orignal named range - to the import, ie CityID and EventID
--- to their corresponding fields in Access.

- Excel range to import: MyExcelRange
This range begins in column C in Excel. *** Does *not* contain the
following in Excel, both of which need to be imported to Access, as many rows
as required:

- Excel 1-cell range to add/imply upon import to Access - in the 1st field
to the left in Access [column B *IF* it were in Excel] : EventID

- Excel 1-cell range to add/imply upon import to Access - in the 2nd field
to the left in Access [column A *IF* it were in Excel] : CityID

Of course, this could be physically re-constructed on another tab in Excel,
but ...
a) This schema is required for multiple tabs.
b) It would be redundantly using large groups of data.
b) The workbook size would start to become prohibitive.

Any assistance would be greatly appreciated.
And please let me know if this needs clarification.
Regards,
- Mike

 

[Dick Kusleika]

Dick,
Thanx for the reply.

Am merely using the Access "Get External Data", then "from Excel".
It's a straight import, not a link [although at some point in the future
will need to do that].

A few clarifying notes:
- The Access table has 6 fields, the first two being EventID and CityID.

- The Excel range has 4 fields in columns C thru F [although those fields
could be in any 4 contiguous columns]. Those fields exclude EventID and
CityID.

- EventID and CityID are both named, 1-cell ranges on another worksheet in
the same Excel workbook.

- It is imperative for the Access table that the values in these 1-cell
ranges [EventID and CityID] "hit" the import from Excel, in as many rows as
necessary.

*** ALSO NOTE - there are dozens of different workbooks containing these
same
ranges that eventually will need to be imported into the Access table.
The values for each of the three ranges are different in each workbook.

Does the above help??

I assume you mean you want the Access table to have 6 columns, but it
doesn't because you're importing from Excel.

Three options, as I see it:

1) Insert two columns in front of column C, refer to the CityID and EventID
in a formula in those columns, and extend your named range to include those
columns. This is the straightforward and obvious answer. You haven't said
why you haven't done this, but I assume you can't add the columns for some
reason. Would it matter if they were hidden?

2) Make two linked tables in Access; one with your C:F range and one that
contains the CityID and EventID. If those two cells aren't side-by-side,
you'll need to make an area somewhere that gets them side-by-side and create
a range name that covers both of them (Access doesn't like single cell range
names for some reason).

Now create a query in Access with these two tables and NO JOINS. This will
give you a recordset like you want.

3) Don't link the tables via the Access UI. Create the tables in VBA using
ADO. This will give you maximum flexibility, but it's the most complex of
the options.

If any of those options sound good and you need more details or an example,
post back.

 

[kove]

When importing a named range from Excel into Access...
- columns C thru J, rows starting at 6 but ending differently all the time -
.. am looking to add 2 named 1-cell ranges from Excel - that are *not*
included in the orignal named range - to the import, ie CityID and EventID
--- to their corresponding fields in Access.

- Excel range to import: MyExcelRange
This range begins in column C in Excel. *** Does *not* contain the
following in Excel, both of which need to be imported to Access, as many rows
as required:

- Excel 1-cell range to add/imply upon import to Access - in the 1st field
to the left in Access [column B *IF* it were in Excel] : EventID

- Excel 1-cell range to add/imply upon import to Access - in the 2nd field
to the left in Access [column A *IF* it were in Excel] : CityID

Of course, this could be physically re-constructed on another tab in Excel,
but ...
a) This schema is required for multiple tabs.
b) It would be redundantly using large groups of data.
b) The workbook size would start to become prohibitive.

Any assistance would be greatly appreciated.
And please let me know if this needs clarification.
Regards,
- Mike

 

[MikeF]

Dick,

Re your #1, as follows is in my original msg:

Of course, this could be physically re-constructed on another tab in Excel,

It may end up though, that I rebuild everything to include the two 1-cell
ranges on every worksheet.
But as stated above, it would merely be using hundreds of rows of redundant
data unnecessarily, on multiple tabs in each workbook.
In attempting to keep an already-very-large file-size in check, am hoping
for a more elegant solution.


Prefer your suggestion #3 as follows.
An example would be sincerely appreciated.

3) Don't link the tables via the Access UI. Create the tables in VBA using
ADO. This will give you maximum flexibility, but it's the most complex of
the options.

Regards and thanx.
- Mike

Dick,
Thanx for the reply.

Am merely using the Access "Get External Data", then "from Excel".
It's a straight import, not a link [although at some point in the future
will need to do that].

A few clarifying notes:
- The Access table has 6 fields, the first two being EventID and CityID.

- The Excel range has 4 fields in columns C thru F [although those fields
could be in any 4 contiguous columns]. Those fields exclude EventID and
CityID.

- EventID and CityID are both named, 1-cell ranges on another worksheet in
the same Excel workbook.

- It is imperative for the Access table that the values in these 1-cell
ranges [EventID and CityID] "hit" the import from Excel, in as many rows as
necessary.

*** ALSO NOTE - there are dozens of different workbooks containing these
same
ranges that eventually will need to be imported into the Access table.
The values for each of the three ranges are different in each workbook.

Does the above help??

I assume you mean you want the Access table to have 6 columns, but it
doesn't because you're importing from Excel.

Three options, as I see it:

1) Insert two columns in front of column C, refer to the CityID and EventID
in a formula in those columns, and extend your named range to include those
columns. This is the straightforward and obvious answer. You haven't said
why you haven't done this, but I assume you can't add the columns for some
reason. Would it matter if they were hidden?

2) Make two linked tables in Access; one with your C:F range and one that
contains the CityID and EventID. If those two cells aren't side-by-side,
you'll need to make an area somewhere that gets them side-by-side and create
a range name that covers both of them (Access doesn't like single cell range
names for some reason).

Now create a query in Access with these two tables and NO JOINS. This will
give you a recordset like you want.

3) Don't link the tables via the Access UI. Create the tables in VBA using
ADO. This will give you maximum flexibility, but it's the most complex of
the options.

If any of those options sound good and you need more details or an example,
post back.

 

[Dick Kusleika]

Prefer your suggestion #3 as follows.
An example would be sincerely appreciated.

3) Don't link the tables via the Access UI. Create the tables in VBA using
ADO. This will give you maximum flexibility, but it's the most complex of
the options.

Here's the basics Mike. Set a reference to Microsoft ActiveX Data Objects.

Sub MakeAccessTable()

Dim sqlMake As String
Dim sqlDelete As String
Dim sqlInsert As String
Dim sCon As String
Dim rRow As Range
Dim rCell As Range
Dim rCity As Range
Dim rEvent As Range
Dim adCon As ADODB.Connection

Set rCity = Sheet1.Range("CityID")
Set rEvent = Sheet1.Range("EventID")
Set adCon = New ADODB.Connection

sCon = "DSN=MS Access Database;DBQ=C:\Documents and Settings\Dick\"
sCon = sCon & "My Documents\testimport.mdb;DefaultDir=C:\Documents "
sCon = sCon & "and Settings\Dick\My Documents;DriverId=25;FIL=MS "
sCon = sCon & "Access;MaxBufferSize=2048;PageTimeout=5;"

sqlMake = "CREATE TABLE tblMyRange (CityID Long, EventID Long, Field3 "
sqlMake = sqlMake & "Long, Field4 Long, Field5 Long, Field6 Long)"

sqlDelete = "DROP TABLE tblMyRange"

adCon.Open sCon
On Error Resume Next
adCon.Execute sqlDelete
On Error GoTo 0
adCon.Execute sqlMake

For Each rRow In Sheet2.Range("MyRange").Rows
sqlInsert = "INSERT INTO tblMyRange VALUES (" & _
rCity.Value & ", " & rEvent.Value & ", "

For Each rCell In rRow.Cells
sqlInsert = sqlInsert & rCell.Value & ", "
Next rCell

sqlInsert = Left$(sqlInsert, Len(sqlInsert) - 2) & ")"
adCon.Execute sqlInsert
Next rRow

adCon.Close

End Sub