array to access
 

Would anybody know a good example of how to do this:
Dump a large (too large for the sheet) 2-D variant array in a newly made
Access table.
This has to be done from Excel and I would like to use ADO, not DAO.
If needed I could get the datatypes for all the columns. I have the field
names.
Thanks for any advice.

RBS

array to access
 

OK, have worked this out now with DAO:

Option Explicit

Sub Array2Access()

Dim Array1
Dim i As Long
Dim Db1 As DAO.Database
Dim tdfNew As TableDef
Dim Rs1 As DAO.Recordset

'make sure the file is not there yet
If Len(Dir("C:\ExcelTest.mdb")) 0 Then
Kill "C:\ExcelTest.mdb"
End If

'First, create the database.
Set Db1 = DBEngine.CreateDatabase("C:\ExcelTest.mdb", _
dbLangGeneral)

'Create a new TableDef object.
Set tdfNew = Db1.CreateTableDef("TestTable")

With tdfNew
' Create fields and append them to the new TableDef
' object. This must be done before appending the
' TableDef object to the TableDefs collection database.
.Fields.Append .CreateField("FruitType", dbText)
.Fields.Append .CreateField("Price", dbInteger)
.Fields.Append .CreateField("Ratio", dbDouble)

' Append the new TableDef object database.
Db1.TableDefs.Append tdfNew
End With

'Then, open the recordset.
Set Rs1 = Db1.OpenRecordset("TestTable", dbOpenDynaset)

'Read a worksheet range into an array.
Array1 = Range("Test").Value

'Then write the data from the array to the recordset.
'Note that for each new record, you must first call Addnew
'then set the value property of the fields, and then call Update.
For i = 1 To UBound(Array1)
With Rs1
.AddNew
.Fields("FruitType") = Array1(i, 1)
.Fields("Price") = Array1(i, 2)
.Fields("Ratio") = Array1(i, 3)
.Update
End With
Next

'Close the database.
Db1.Close

OpenAccessTable

End Sub


Sub OpenAccessTable()

Dim objApp As Object
Dim accApp As Access.Application

Set accApp = Nothing
Set accApp = CreateObject("Access.Application")

With accApp
On Error Resume Next
Set objApp = accApp
objApp.AutomationSecurity = 2 'msoAutomationSecurityLow
On Error GoTo 0

.OpenCurrentDatabase "C:\ExcelFiles\ExcelTest.mdb"
.DoCmd.OpenTable "TestTable", acViewNormal, acReadOnly
.Visible = True
.DoCmd.Maximize
End With

Set accApp = Nothing

End Sub


This is quite simple and I just have to see now if I can do it without
specifying
the datatypes and I have to see how fast this is with large arrays.
Of course there is a problem if Access is not installed or would this still
be OK as
long as the Jet database engine is there?


RBS




"RB Smissaert" wrote in message
...
Would anybody know a good example of how to do this:
Dump a large (too large for the sheet) 2-D variant array in a newly made
Access table.
This has to be done from Excel and I would like to use ADO, not DAO.
If needed I could get the datatypes for all the columns. I have the field
names.
Thanks for any advice.

RBS

array to access
 

You only need Jet and DAO to the best of my knowledge.

--
Regards,
Tom Ogilvy

"RB Smissaert" wrote in message
...
OK, have worked this out now with DAO:

Option Explicit

Sub Array2Access()

Dim Array1
Dim i As Long
Dim Db1 As DAO.Database
Dim tdfNew As TableDef
Dim Rs1 As DAO.Recordset

'make sure the file is not there yet
If Len(Dir("C:\ExcelTest.mdb")) 0 Then
Kill "C:\ExcelTest.mdb"
End If

'First, create the database.
Set Db1 = DBEngine.CreateDatabase("C:\ExcelTest.mdb", _
dbLangGeneral)

'Create a new TableDef object.
Set tdfNew = Db1.CreateTableDef("TestTable")

With tdfNew
' Create fields and append them to the new TableDef
' object. This must be done before appending the
' TableDef object to the TableDefs collection database.
.Fields.Append .CreateField("FruitType", dbText)
.Fields.Append .CreateField("Price", dbInteger)
.Fields.Append .CreateField("Ratio", dbDouble)

' Append the new TableDef object database.
Db1.TableDefs.Append tdfNew
End With

'Then, open the recordset.
Set Rs1 = Db1.OpenRecordset("TestTable", dbOpenDynaset)

'Read a worksheet range into an array.
Array1 = Range("Test").Value

'Then write the data from the array to the recordset.
'Note that for each new record, you must first call Addnew
'then set the value property of the fields, and then call Update.
For i = 1 To UBound(Array1)
With Rs1
.AddNew
.Fields("FruitType") = Array1(i, 1)
.Fields("Price") = Array1(i, 2)
.Fields("Ratio") = Array1(i, 3)
.Update
End With
Next

'Close the database.
Db1.Close

OpenAccessTable

End Sub


Sub OpenAccessTable()

Dim objApp As Object
Dim accApp As Access.Application

Set accApp = Nothing
Set accApp = CreateObject("Access.Application")

With accApp
On Error Resume Next
Set objApp = accApp
objApp.AutomationSecurity = 2 'msoAutomationSecurityLow
On Error GoTo 0

.OpenCurrentDatabase "C:\ExcelFiles\ExcelTest.mdb"
.DoCmd.OpenTable "TestTable", acViewNormal, acReadOnly
.Visible = True
.DoCmd.Maximize
End With

Set accApp = Nothing

End Sub


This is quite simple and I just have to see now if I can do it without
specifying
the datatypes and I have to see how fast this is with large arrays.
Of course there is a problem if Access is not installed or would this
still
be OK as
long as the Jet database engine is there?


RBS




"RB Smissaert" wrote in message
...
Would anybody know a good example of how to do this:
Dump a large (too large for the sheet) 2-D variant array in a newly made
Access table.
This has to be done from Excel and I would like to use ADO, not DAO.
If needed I could get the datatypes for all the columns. I have the
field
names.
Thanks for any advice.

RBS

array to access
 

I take it if they have Excel they will have DAO.
How about this bit of code though:

Set accApp = CreateObject("Access.Application")

Will it work even if Access isn't installed?
Just tested performance with a 80000 rows and 3 columns array of text and
numbers and that is fine.
It will still show the Access table in a few seconds.

RBS


"Tom Ogilvy" wrote in message
...
You only need Jet and DAO to the best of my knowledge.

--
Regards,
Tom Ogilvy

"RB Smissaert" wrote in message
...
OK, have worked this out now with DAO:

Option Explicit

Sub Array2Access()

Dim Array1
Dim i As Long
Dim Db1 As DAO.Database
Dim tdfNew As TableDef
Dim Rs1 As DAO.Recordset

'make sure the file is not there yet
If Len(Dir("C:\ExcelTest.mdb")) 0 Then
Kill "C:\ExcelTest.mdb"
End If

'First, create the database.
Set Db1 = DBEngine.CreateDatabase("C:\ExcelTest.mdb", _
dbLangGeneral)

'Create a new TableDef object.
Set tdfNew = Db1.CreateTableDef("TestTable")

With tdfNew
' Create fields and append them to the new TableDef
' object. This must be done before appending the
' TableDef object to the TableDefs collection database.
.Fields.Append .CreateField("FruitType", dbText)
.Fields.Append .CreateField("Price", dbInteger)
.Fields.Append .CreateField("Ratio", dbDouble)

' Append the new TableDef object database.
Db1.TableDefs.Append tdfNew
End With

'Then, open the recordset.
Set Rs1 = Db1.OpenRecordset("TestTable", dbOpenDynaset)

'Read a worksheet range into an array.
Array1 = Range("Test").Value

'Then write the data from the array to the recordset.
'Note that for each new record, you must first call Addnew
'then set the value property of the fields, and then call Update.
For i = 1 To UBound(Array1)
With Rs1
.AddNew
.Fields("FruitType") = Array1(i, 1)
.Fields("Price") = Array1(i, 2)
.Fields("Ratio") = Array1(i, 3)
.Update
End With
Next

'Close the database.
Db1.Close

OpenAccessTable

End Sub


Sub OpenAccessTable()

Dim objApp As Object
Dim accApp As Access.Application

Set accApp = Nothing
Set accApp = CreateObject("Access.Application")

With accApp
On Error Resume Next
Set objApp = accApp
objApp.AutomationSecurity = 2 'msoAutomationSecurityLow
On Error GoTo 0

.OpenCurrentDatabase "C:\ExcelFiles\ExcelTest.mdb"
.DoCmd.OpenTable "TestTable", acViewNormal, acReadOnly
.Visible = True
.DoCmd.Maximize
End With

Set accApp = Nothing

End Sub


This is quite simple and I just have to see now if I can do it without
specifying
the datatypes and I have to see how fast this is with large arrays.
Of course there is a problem if Access is not installed or would this
still
be OK as
long as the Jet database engine is there?


RBS




"RB Smissaert" wrote in message
...
Would anybody know a good example of how to do this:
Dump a large (too large for the sheet) 2-D variant array in a newly
made
Access table.
This has to be done from Excel and I would like to use ADO, not DAO.
If needed I could get the datatypes for all the columns. I have the
field
names.
Thanks for any advice.

RBS

array to access
 

No, that won't worked without access. I assumed you were just doing that to
test the results.

--
Regards,
Tom Ogilvy



"RB Smissaert" wrote in message
...
I take it if they have Excel they will have DAO.
How about this bit of code though:

Set accApp = CreateObject("Access.Application")

Will it work even if Access isn't installed?
Just tested performance with a 80000 rows and 3 columns array of text and
numbers and that is fine.
It will still show the Access table in a few seconds.

RBS


"Tom Ogilvy" wrote in message
...
You only need Jet and DAO to the best of my knowledge.

--
Regards,
Tom Ogilvy

"RB Smissaert" wrote in message
...
OK, have worked this out now with DAO:

Option Explicit

Sub Array2Access()

Dim Array1
Dim i As Long
Dim Db1 As DAO.Database
Dim tdfNew As TableDef
Dim Rs1 As DAO.Recordset

'make sure the file is not there yet
If Len(Dir("C:\ExcelTest.mdb")) 0 Then
Kill "C:\ExcelTest.mdb"
End If

'First, create the database.
Set Db1 = DBEngine.CreateDatabase("C:\ExcelTest.mdb", _
dbLangGeneral)

'Create a new TableDef object.
Set tdfNew = Db1.CreateTableDef("TestTable")

With tdfNew
' Create fields and append them to the new TableDef
' object. This must be done before appending the
' TableDef object to the TableDefs collection database.
.Fields.Append .CreateField("FruitType", dbText)
.Fields.Append .CreateField("Price", dbInteger)
.Fields.Append .CreateField("Ratio", dbDouble)

' Append the new TableDef object database.
Db1.TableDefs.Append tdfNew
End With

'Then, open the recordset.
Set Rs1 = Db1.OpenRecordset("TestTable", dbOpenDynaset)

'Read a worksheet range into an array.
Array1 = Range("Test").Value

'Then write the data from the array to the recordset.
'Note that for each new record, you must first call Addnew
'then set the value property of the fields, and then call Update.
For i = 1 To UBound(Array1)
With Rs1
.AddNew
.Fields("FruitType") = Array1(i, 1)
.Fields("Price") = Array1(i, 2)
.Fields("Ratio") = Array1(i, 3)
.Update
End With
Next

'Close the database.
Db1.Close

OpenAccessTable

End Sub


Sub OpenAccessTable()

Dim objApp As Object
Dim accApp As Access.Application

Set accApp = Nothing
Set accApp = CreateObject("Access.Application")

With accApp
On Error Resume Next
Set objApp = accApp
objApp.AutomationSecurity = 2 'msoAutomationSecurityLow
On Error GoTo 0

.OpenCurrentDatabase "C:\ExcelFiles\ExcelTest.mdb"
.DoCmd.OpenTable "TestTable", acViewNormal, acReadOnly
.Visible = True
.DoCmd.Maximize
End With

Set accApp = Nothing

End Sub


This is quite simple and I just have to see now if I can do it without
specifying
the datatypes and I have to see how fast this is with large arrays.
Of course there is a problem if Access is not installed or would this
still
be OK as
long as the Jet database engine is there?


RBS




"RB Smissaert" wrote in message
...
Would anybody know a good example of how to do this:
Dump a large (too large for the sheet) 2-D variant array in a newly
made
Access table.
This has to be done from Excel and I would like to use ADO, not DAO.
If needed I could get the datatypes for all the columns. I have the
field
names.
Thanks for any advice.

RBS

array to access
 

So how do I show the .mdb file if Access isn't installed?
I suppose I could show it as text, but then I might as well dump the array
to text.

RBS


"Tom Ogilvy" wrote in message
...
No, that won't worked without access. I assumed you were just doing that
to
test the results.

--
Regards,
Tom Ogilvy



"RB Smissaert" wrote in message
...
I take it if they have Excel they will have DAO.
How about this bit of code though:

Set accApp = CreateObject("Access.Application")

Will it work even if Access isn't installed?
Just tested performance with a 80000 rows and 3 columns array of text and
numbers and that is fine.
It will still show the Access table in a few seconds.

RBS


"Tom Ogilvy" wrote in message
...
You only need Jet and DAO to the best of my knowledge.

--
Regards,
Tom Ogilvy

"RB Smissaert" wrote in message
...
OK, have worked this out now with DAO:

Option Explicit

Sub Array2Access()

Dim Array1
Dim i As Long
Dim Db1 As DAO.Database
Dim tdfNew As TableDef
Dim Rs1 As DAO.Recordset

'make sure the file is not there yet
If Len(Dir("C:\ExcelTest.mdb")) 0 Then
Kill "C:\ExcelTest.mdb"
End If

'First, create the database.
Set Db1 = DBEngine.CreateDatabase("C:\ExcelTest.mdb", _
dbLangGeneral)

'Create a new TableDef object.
Set tdfNew = Db1.CreateTableDef("TestTable")

With tdfNew
' Create fields and append them to the new TableDef
' object. This must be done before appending the
' TableDef object to the TableDefs collection database.
.Fields.Append .CreateField("FruitType", dbText)
.Fields.Append .CreateField("Price", dbInteger)
.Fields.Append .CreateField("Ratio", dbDouble)

' Append the new TableDef object database.
Db1.TableDefs.Append tdfNew
End With

'Then, open the recordset.
Set Rs1 = Db1.OpenRecordset("TestTable", dbOpenDynaset)

'Read a worksheet range into an array.
Array1 = Range("Test").Value

'Then write the data from the array to the recordset.
'Note that for each new record, you must first call Addnew
'then set the value property of the fields, and then call Update.
For i = 1 To UBound(Array1)
With Rs1
.AddNew
.Fields("FruitType") = Array1(i, 1)
.Fields("Price") = Array1(i, 2)
.Fields("Ratio") = Array1(i, 3)
.Update
End With
Next

'Close the database.
Db1.Close

OpenAccessTable

End Sub


Sub OpenAccessTable()

Dim objApp As Object
Dim accApp As Access.Application

Set accApp = Nothing
Set accApp = CreateObject("Access.Application")

With accApp
On Error Resume Next
Set objApp = accApp
objApp.AutomationSecurity = 2 'msoAutomationSecurityLow
On Error GoTo 0

.OpenCurrentDatabase "C:\ExcelFiles\ExcelTest.mdb"
.DoCmd.OpenTable "TestTable", acViewNormal, acReadOnly
.Visible = True
.DoCmd.Maximize
End With

Set accApp = Nothing

End Sub


This is quite simple and I just have to see now if I can do it without
specifying
the datatypes and I have to see how fast this is with large arrays.
Of course there is a problem if Access is not installed or would this
still
be OK as
long as the Jet database engine is there?


RBS




"RB Smissaert" wrote in message
...
Would anybody know a good example of how to do this:
Dump a large (too large for the sheet) 2-D variant array in a newly
made
Access table.
This has to be done from Excel and I would like to use ADO, not DAO.
If needed I could get the datatypes for all the columns. I have the
field
names.
Thanks for any advice.

RBS

array to access
 

Maybe distribute the access runtime with a custom app to show it. I don't
know what you are trying to achive. However, on a machine that typically
does not have access, I wouldn't know what you use to show an MDB file.
Perhaps there is a viewer like for excel.

--
Regards,
Tom Ogilvy

"RB Smissaert" wrote in message
...
So how do I show the .mdb file if Access isn't installed?
I suppose I could show it as text, but then I might as well dump the array
to text.

RBS


"Tom Ogilvy" wrote in message
...
No, that won't worked without access. I assumed you were just doing that
to
test the results.

--
Regards,
Tom Ogilvy



"RB Smissaert" wrote in message
...
I take it if they have Excel they will have DAO.
How about this bit of code though:

Set accApp = CreateObject("Access.Application")

Will it work even if Access isn't installed?
Just tested performance with a 80000 rows and 3 columns array of text
and
numbers and that is fine.
It will still show the Access table in a few seconds.

RBS


"Tom Ogilvy" wrote in message
...
You only need Jet and DAO to the best of my knowledge.

--
Regards,
Tom Ogilvy

"RB Smissaert" wrote in message
...
OK, have worked this out now with DAO:

Option Explicit

Sub Array2Access()

Dim Array1
Dim i As Long
Dim Db1 As DAO.Database
Dim tdfNew As TableDef
Dim Rs1 As DAO.Recordset

'make sure the file is not there yet
If Len(Dir("C:\ExcelTest.mdb")) 0 Then
Kill "C:\ExcelTest.mdb"
End If

'First, create the database.
Set Db1 = DBEngine.CreateDatabase("C:\ExcelTest.mdb", _
dbLangGeneral)

'Create a new TableDef object.
Set tdfNew = Db1.CreateTableDef("TestTable")

With tdfNew
' Create fields and append them to the new TableDef
' object. This must be done before appending the
' TableDef object to the TableDefs collection database.
.Fields.Append .CreateField("FruitType", dbText)
.Fields.Append .CreateField("Price", dbInteger)
.Fields.Append .CreateField("Ratio", dbDouble)

' Append the new TableDef object database.
Db1.TableDefs.Append tdfNew
End With

'Then, open the recordset.
Set Rs1 = Db1.OpenRecordset("TestTable", dbOpenDynaset)

'Read a worksheet range into an array.
Array1 = Range("Test").Value

'Then write the data from the array to the recordset.
'Note that for each new record, you must first call Addnew
'then set the value property of the fields, and then call
Update.
For i = 1 To UBound(Array1)
With Rs1
.AddNew
.Fields("FruitType") = Array1(i, 1)
.Fields("Price") = Array1(i, 2)
.Fields("Ratio") = Array1(i, 3)
.Update
End With
Next

'Close the database.
Db1.Close

OpenAccessTable

End Sub


Sub OpenAccessTable()

Dim objApp As Object
Dim accApp As Access.Application

Set accApp = Nothing
Set accApp = CreateObject("Access.Application")

With accApp
On Error Resume Next
Set objApp = accApp
objApp.AutomationSecurity = 2
'msoAutomationSecurityLow
On Error GoTo 0

.OpenCurrentDatabase "C:\ExcelFiles\ExcelTest.mdb"
.DoCmd.OpenTable "TestTable", acViewNormal, acReadOnly
.Visible = True
.DoCmd.Maximize
End With

Set accApp = Nothing

End Sub


This is quite simple and I just have to see now if I can do it
without
specifying
the datatypes and I have to see how fast this is with large arrays.
Of course there is a problem if Access is not installed or would
this
still
be OK as
long as the Jet database engine is there?


RBS




"RB Smissaert" wrote in message
...
Would anybody know a good example of how to do this:
Dump a large (too large for the sheet) 2-D variant array in a
newly
made
Access table.
This has to be done from Excel and I would like to use ADO, not
DAO.
If needed I could get the datatypes for all the columns. I have
the
field
names.
Thanks for any advice.

RBS

array to access
 

What I am trying to achieve is simply showing an array (a data table) that
is too big for the sheet.
Probably it is best to make this an optional feature for people with Access
or otherwise show it
as a text file. Will have a look into an Access viewer.

RBS


"Tom Ogilvy" wrote in message
...
Maybe distribute the access runtime with a custom app to show it. I don't
know what you are trying to achive. However, on a machine that typically
does not have access, I wouldn't know what you use to show an MDB file.
Perhaps there is a viewer like for excel.

--
Regards,
Tom Ogilvy

"RB Smissaert" wrote in message
...
So how do I show the .mdb file if Access isn't installed?
I suppose I could show it as text, but then I might as well dump the
array
to text.

RBS


"Tom Ogilvy" wrote in message
...
No, that won't worked without access. I assumed you were just doing
that
to
test the results.

--
Regards,
Tom Ogilvy



"RB Smissaert" wrote in message
...
I take it if they have Excel they will have DAO.
How about this bit of code though:

Set accApp = CreateObject("Access.Application")

Will it work even if Access isn't installed?
Just tested performance with a 80000 rows and 3 columns array of text
and
numbers and that is fine.
It will still show the Access table in a few seconds.

RBS


"Tom Ogilvy" wrote in message
...
You only need Jet and DAO to the best of my knowledge.

--
Regards,
Tom Ogilvy

"RB Smissaert" wrote in message
...
OK, have worked this out now with DAO:

Option Explicit

Sub Array2Access()

Dim Array1
Dim i As Long
Dim Db1 As DAO.Database
Dim tdfNew As TableDef
Dim Rs1 As DAO.Recordset

'make sure the file is not there yet
If Len(Dir("C:\ExcelTest.mdb")) 0 Then
Kill "C:\ExcelTest.mdb"
End If

'First, create the database.
Set Db1 = DBEngine.CreateDatabase("C:\ExcelTest.mdb", _
dbLangGeneral)

'Create a new TableDef object.
Set tdfNew = Db1.CreateTableDef("TestTable")

With tdfNew
' Create fields and append them to the new TableDef
' object. This must be done before appending the
' TableDef object to the TableDefs collection database.
.Fields.Append .CreateField("FruitType", dbText)
.Fields.Append .CreateField("Price", dbInteger)
.Fields.Append .CreateField("Ratio", dbDouble)

' Append the new TableDef object database.
Db1.TableDefs.Append tdfNew
End With

'Then, open the recordset.
Set Rs1 = Db1.OpenRecordset("TestTable", dbOpenDynaset)

'Read a worksheet range into an array.
Array1 = Range("Test").Value

'Then write the data from the array to the recordset.
'Note that for each new record, you must first call Addnew
'then set the value property of the fields, and then call
Update.
For i = 1 To UBound(Array1)
With Rs1
.AddNew
.Fields("FruitType") = Array1(i, 1)
.Fields("Price") = Array1(i, 2)
.Fields("Ratio") = Array1(i, 3)
.Update
End With
Next

'Close the database.
Db1.Close

OpenAccessTable

End Sub


Sub OpenAccessTable()

Dim objApp As Object
Dim accApp As Access.Application

Set accApp = Nothing
Set accApp = CreateObject("Access.Application")

With accApp
On Error Resume Next
Set objApp = accApp
objApp.AutomationSecurity = 2
'msoAutomationSecurityLow
On Error GoTo 0

.OpenCurrentDatabase "C:\ExcelFiles\ExcelTest.mdb"
.DoCmd.OpenTable "TestTable", acViewNormal, acReadOnly
.Visible = True
.DoCmd.Maximize
End With

Set accApp = Nothing

End Sub


This is quite simple and I just have to see now if I can do it
without
specifying
the datatypes and I have to see how fast this is with large arrays.
Of course there is a problem if Access is not installed or would
this
still
be OK as
long as the Jet database engine is there?


RBS




"RB Smissaert" wrote in message
...
Would anybody know a good example of how to do this:
Dump a large (too large for the sheet) 2-D variant array in a
newly
made
Access table.
This has to be done from Excel and I would like to use ADO, not
DAO.
If needed I could get the datatypes for all the columns. I have
the
field
names.
Thanks for any advice.

RBS

array to access
 

Hi,
could you use the Spreadsheet control on the worksheet or a form from
Microsoft Web Components? This has more rows and columns than Excel....

ActiveSheet.OLEObjects.Add(ClassType:="OWC.Spreads heet.9",
Link:=False, _
DisplayAsIcon:=False).Select

hth
O

array to access
 

Thanks, that looks like an interesting option.
How would I get an array in that Spreadsheet control?

If this were all possible it might be better than using Access as populating
the
table is a bit slow. Maybe I am doing some wrong in the loop to populate
the Recordset:


Sub Array2Access()

Dim Db1 As DAO.Database
Dim tdfNew As TableDef
Dim Rs1 As DAO.Recordset
Dim n As Long
Dim i As Long
Dim c As Long

'make sure is not there yet
If Len(Dir("C:\ExcelTest.mdb")) 0 Then
Kill "C:\ExcelTest.mdb"
End If

'First, create the database.
Set Db1 = DBEngine.CreateDatabase("C:\ExcelTest.mdb", _
dbLangGeneral)

'Create a new TableDef object.
Set tdfNew = Db1.CreateTableDef("TestTable")

With tdfNew
' Create fields and append them to the new TableDef
' object. This must be done before appending the
' TableDef object to the TableDefs collection database.

'field PATIENT_ID, don't allow zero-length here
.Fields.Append .CreateField(fieldArray(1), dbLong)

'remaining ID fields
For c = 2 To SeparatorArray(1, 1) - 1
.Fields.Append .CreateField(fieldArray(c), dbText)
.Fields(fieldArray(c)).AllowZeroLength = True
Next

'non-ID fields or secondary ID fields
For c = SeparatorArray(1, 1) To FieldCount
.Fields.Append .CreateField(fieldArray(c) & c, dbText)
.Fields(fieldArray(c) & c).AllowZeroLength = True
Next
'Append the new TableDef object database.
Db1.TableDefs.Append tdfNew
End With

'Then, open the recordset.
Set Rs1 = Db1.OpenRecordset("TestTable", dbOpenDynaset)

'Then write the data from the array to the recordset.
'Note that for each new record, you must first call Addnew
'then set the value property of the fields, and then call Update.
With Rs1
For i = 1 To URowCount
ShowProgressMessage strStatusIndent & _
"Making Access table, please wait ... " & _
i & "/" & URowCount
.AddNew
.Fields(fieldArray(1)) = TableArray(i, 1)
For c = 2 To SeparatorArray(1, 1) - 1
.Fields(fieldArray(c)) = TableArray(i, c)
Next
For c = SeparatorArray(1, 1) To FieldCount
.Fields(fieldArray(c) & c) = TableArray(i, c)
Next
.Update
Next
'this won't update in the form
'MainForm.ProgressBar1.Value = ((i - 1) / URowCount) * 100
'DoEvents
End With

'Close the database.
Db1.Close

OpenAccessTable

End Sub


RBS


"OJ" wrote in message
ups.com...
Hi,
could you use the Spreadsheet control on the worksheet or a form from
Microsoft Web Components? This has more rows and columns than Excel....

ActiveSheet.OLEObjects.Add(ClassType:="OWC.Spreads heet.9",
Link:=False, _
DisplayAsIcon:=False).Select

hth
O

array to access
 

"RB Smissaert" wrote in message
...
What I am trying to achieve is simply showing an array (a data table) that
is too big for the sheet.
Probably it is best to make this an optional feature for people with
Access
or otherwise show it
as a text file. Will have a look into an Access viewer.


just as idea, don't know how rich viewer functions you need,
but if your data file has fixed line length maybe to use pure file i/o
functions
to read in a couple of lines of text file and show them,
also to have simple controls for basic navigation, scroll up-down.

for example, i made a sub (based on raw file i/o) for reading dbf file much
larger than excel row count limit. this helps me to do the job without
having a need for any software except excel.
this comes from the old win3.1 days, but is still compatible!

array to access
 

Thanks for the suggestion, but the output needs spreadsheet-like
capabilities such as sorting, formatting,
deleting columns, copy and paste etc.
I have sorted out the Access output and most users will have this as part of
Office, so I think this is kind of
done now.
I am still interested in the OWC spreadsheet control, but I can't find much
information about that.

RBS


"sali" wrote in message
...
"RB Smissaert" wrote in message
...
What I am trying to achieve is simply showing an array (a data table)
that
is too big for the sheet.
Probably it is best to make this an optional feature for people with
Access
or otherwise show it
as a text file. Will have a look into an Access viewer.


just as idea, don't know how rich viewer functions you need,
but if your data file has fixed line length maybe to use pure file i/o
functions
to read in a couple of lines of text file and show them,
also to have simple controls for basic navigation, scroll up-down.

for example, i made a sub (based on raw file i/o) for reading dbf file
much
larger than excel row count limit. this helps me to do the job without
having a need for any software except excel.
this comes from the old win3.1 days, but is still compatible!