CODEWITHSUNDEEP
excelms-accessvba
Another Man
Another Man

Reputation: 41

How can I read multiple columns from an Access database?

I have been assigned the task to calculate some values from Access and store them to Excel. My code works if I use a single-column database.

My code looks like this:

With Recordset
        Source = "SELECT tbl_cog.[Latitude] FROM tbl_cog WHERE Company='Bandung Food Truck Festival Members'"
        .Open Source:=Source, ActiveConnection:=Connection
        For Col = 0 To Recordset.Fields.Count - 1
            TextBox1.Value = Recordset.Fields(Col).Value
        Next
    End With

But when I want to read multiple columns, my code just reads one column. My code looks like this:

With Recordset
        Source = "SELECT tbl_cog.[Latitude], tbl_cog.[Longitude] FROM tbl_cog WHERE Company='Bandung Food Truck Festival Members'"
        .Open Source:=Source, ActiveConnection:=Connection
        For Col = 0 To Recordset.Fields.Count - 1
            TextBox1.Value = Recordset.Fields(Col).Value
            TextBox2.Value = Recordset.Fields(Col).Value
        Next
    End With

UPDATE:

My program with 1 column like this: https://prntscr.com/a90g5z

My program with 2 column like this: https://prntscr.com/a90gpi

My database access like this: https://prntscr.com/a90h0q

Upvotes: 1

Views: 1230

Answers (2)

Alexander Bell
Alexander Bell

Reputation: 7918

Assuming that there is only one record in Recordset, then you should correct your code like shown in the following snippet:

TextBox1.Value = Recordset.Fields(0).Value
TextBox2.Value = Recordset.Fields(1).Value

and so on (in case you have more than two fields). Apparently, you do not need For loop to complete this task.

Upvotes: 2

Cornel
Cornel

Reputation: 131

I have used this method to bring data from Access to Excel:

DataArray = Recordset.GetRows()  'all the data from the Select is transferred to an array
nb_rows = UBound(DataArray, 1)  'calculate the number of rows of the array
nb_cols = UBound(DataArray, 2)  'calculate the number of columns of the array

'paste the array to excel
Sheets(1).Range(Cells(1, 1), Cells(nb_rows, nb_cols)).Value = DataArray

'if you want the first 3 columns just replace Cells(nb_rows, nb_cols) with Cells(nb_rows, 3)

Use this code to replace "for col=0 To Recordset.Fields.Count - 1 .... next"

Upvotes: 1

Related Questions