Translation Excel to Access

  • Thread starter Thread starter Varne
  • Start date Start date
V

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
 
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:
Code: 
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
 
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:
Code: 
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
 
Ooops forgot the second dimension used one array instead of two:
MembersAges(i, 1) = rs.MemberName
MembersAges(i, 2) = rs.MemberAge

June7 said:
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:
Code: 
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

Varne said:
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
Click to expand...
Click to expand...
 
Ooops forgot the second dimension used one array instead of two:
MembersAges(i, 1) = rs.MemberName
MembersAges(i, 2) = rs.MemberAge

June7 said:
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:
Code: 
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

Varne said:
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
Click to expand...
Click to expand...
 
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

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

June7 said:
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:
Code: 
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

Varne said:
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
Click to expand...
Click to expand...
Click to expand...
 
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

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

June7 said:
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:
Code: 
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

Varne said:
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
Click to expand...
Click to expand...
Click to expand...
 
Sorry for responding late. I have limited Internet Access.

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

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

June7 said:
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:
Code: 
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

Varne said:
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
Click to expand...
Click to expand...
Click to expand...
 
Sorry for responding late. I have limited Internet Access.

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

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

June7 said:
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:
Code: 
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

Varne said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top