Translation Excel to Access

[Varne]

Hi!

The following VBA codes have references (Cells) to a 2 field Excel table.
Can someone translate for example cells(1,3) suitably to run the codes on an
identical Access table.



Sub MembersData()

Dim Members(32766) As String
Dim MemberName As Integer
Dim Age(32766) As Double
Dim MemberAge As Integer

For MemberName = LBound(Members) To UBound(Members)
Members(MemberName) = Cells(MemberName + 1, 1)
Next
For MemberAge = LBound(Age) To UBound(Age)
Age(MemberAge) = Cells(MemberAge + 1, 2)
Next
Cells(1, 3) = Members(32755)
Cells(1, 4) = Age(32755)

End Sub

 

[June7]

I would do: in VBA, use SQL statement to open a recordset of the table,
populate the array from fields of the recordset. Something like:

Dim rs As ADODB.Recordset
Dim MembersAges(number of members, 2)  'two-dimensional array, must know how
many members will be, if not known do not define bounds and will have to use
redim statement in the loop below
Set rs = New ADODB.Recordset

rs.Open "SELECT * FROM tablename;"
While Not rs.EOF
for i = 0 to UBound(MembersAges)  '0 because Array default base is 0, if
want base 1 must be declared
Members(i) = rs.MemberName
Age(i) = rs.MemberAge
rs.MoveNext
Next
Wend
rs.Close

 

[June7]

Ooops forgot the second dimension used one array instead of two:
MembersAges(i, 1) = rs.MemberName
MembersAges(i, 2) = rs.MemberAge

I would do: in VBA, use SQL statement to open a recordset of the table,
populate the array from fields of the recordset. Something like:

Dim rs As ADODB.Recordset
Dim MembersAges(number of members, 2)  'two-dimensional array, must know how
many members will be, if not known do not define bounds and will have to use
redim statement in the loop below
Set rs = New ADODB.Recordset

rs.Open "SELECT * FROM tablename;"
While Not rs.EOF
for i = 0 to UBound(MembersAges)  '0 because Array default base is 0, if
want base 1 must be declared
Members(i) = rs.MemberName
Age(i) = rs.MemberAge
rs.MoveNext
Next
Wend
rs.Close

Hi!

The following VBA codes have references (Cells) to a 2 field Excel table.
Can someone translate for example cells(1,3) suitably to run the codes on an
identical Access table.



Sub MembersData()

Dim Members(32766) As String
Dim MemberName As Integer
Dim Age(32766) As Double
Dim MemberAge As Integer

For MemberName = LBound(Members) To UBound(Members)
Members(MemberName) = Cells(MemberName + 1, 1)
Next
For MemberAge = LBound(Age) To UBound(Age)
Age(MemberAge) = Cells(MemberAge + 1, 2)
Next
Cells(1, 3) = Members(32755)
Cells(1, 4) = Age(32755)

End Sub

 

[Varne]

Hi!

Array OK but I am not able to establish connection.

Compile error - user defined type not Defined

I tried adding Dim cn As New ADODB.Connection. No use.

Thanks.

M Varnendra

Ooops forgot the second dimension used one array instead of two:
MembersAges(i, 1) = rs.MemberName
MembersAges(i, 2) = rs.MemberAge

I would do: in VBA, use SQL statement to open a recordset of the table,
populate the array from fields of the recordset. Something like:

Dim rs As ADODB.Recordset
Dim MembersAges(number of members, 2)  'two-dimensional array, must know how
many members will be, if not known do not define bounds and will have to use
redim statement in the loop below
Set rs = New ADODB.Recordset

rs.Open "SELECT * FROM tablename;"
While Not rs.EOF
for i = 0 to UBound(MembersAges)  '0 because Array default base is 0, if
want base 1 must be declared
Members(i) = rs.MemberName
Age(i) = rs.MemberAge
rs.MoveNext
Next
Wend
rs.Close

Hi!

The following VBA codes have references (Cells) to a 2 field Excel table.
Can someone translate for example cells(1,3) suitably to run the codes on an
identical Access table.



Sub MembersData()

Dim Members(32766) As String
Dim MemberName As Integer
Dim Age(32766) As Double
Dim MemberAge As Integer

For MemberName = LBound(Members) To UBound(Members)
Members(MemberName) = Cells(MemberName + 1, 1)
Next
For MemberAge = LBound(Age) To UBound(Age)
Age(MemberAge) = Cells(MemberAge + 1, 2)
Next
Cells(1, 3) = Members(32755)
Cells(1, 4) = Age(32755)

End Sub

 

[Varne]

Sorry for responding late. I have limited Internet Access.

I have managed to add ADO library to the object library. So connection works.

Ooops forgot the second dimension used one array instead of two:
MembersAges(i, 1) = rs.MemberName
MembersAges(i, 2) = rs.MemberAge

I would do: in VBA, use SQL statement to open a recordset of the table,
populate the array from fields of the recordset. Something like:

Dim rs As ADODB.Recordset
Dim MembersAges(number of members, 2)  'two-dimensional array, must know how
many members will be, if not known do not define bounds and will have to use
redim statement in the loop below
Set rs = New ADODB.Recordset

rs.Open "SELECT * FROM tablename;"
While Not rs.EOF
for i = 0 to UBound(MembersAges)  '0 because Array default base is 0, if
want base 1 must be declared
Members(i) = rs.MemberName
Age(i) = rs.MemberAge
rs.MoveNext
Next
Wend
rs.Close

Hi!

The following VBA codes have references (Cells) to a 2 field Excel table.
Can someone translate for example cells(1,3) suitably to run the codes on an
identical Access table.



Sub MembersData()

Dim Members(32766) As String
Dim MemberName As Integer
Dim Age(32766) As Double
Dim MemberAge As Integer

For MemberName = LBound(Members) To UBound(Members)
Members(MemberName) = Cells(MemberName + 1, 1)
Next
For MemberAge = LBound(Age) To UBound(Age)
Age(MemberAge) = Cells(MemberAge + 1, 2)
Next
Cells(1, 3) = Members(32755)
Cells(1, 4) = Age(32755)

End Sub