Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
meaning of : IF(Switch; Average(array A, array B); array A) | Excel Worksheet Functions | |||
Access Form In An Access Report (SubForm) Question | Links and Linking in Excel | |||
How to Access Array of Arrays? | Excel Programming | |||
Can I have an array of arrays and access an element simply? | Excel Programming | |||
Access Query Recordet conversion to an Array | Excel Programming |