On Sun, 15 Feb 2009 05:35:00 -0800, MikeF
wrote:



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
--
Dick Kusleika
Microsoft MVP-Excel
http://www.dailydoseofexcel.com