SQL Statement Help

[richardSmith]

I want to use Excel SQL to query a table in access (I have googled and learned I can do this via DAO or ADO). So this is my SQL statement:
[vba]
"Select possibleFields from FieldsinDatabase;"
[/vba]

This would then return the possible fields that could be in the main database. Ideally, I would like to store all of these fields in one variable if possible, and append them to the end of a seperate SQL string that I would be running. So maybe something like:
[vba]
fieldNames = "Select possibleFields from FieldsinDatabase;"
[/vba]

then append fieldNames to a seperate SQL statement so it would be:
[vba]
"Select ReferreeNum, RefereeFName, RefereeLName, RefereeAddress, RefereePhone," & fieldNames & " from MainDatabase
[/vba]

How can I accomplish this via VBA?

[snb]

It's not necessary to make an inventory of all fields in a database to retrieve them.
You can use a wildcard character *

If you want all the fields in table 'tblCustomerInfo' in database 'Test1.mdb' you can use:



[vba]

Sub snb()


' set reference to Microsoft ActiveX Data Objects 2.0 Library
With New Recordset

.Open "SELECT * FROM tblCustomerInfo", "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Test\Test1.mdb"
Sheets(1).Cells(1).CopyFromRecordset .DataSource
End With
End Sub

[/vba]

[Bob Phillips]

Surely, you know the names of the field in your database?

[richardSmith]

I agree the set-up is atrocious! However, there are about 500 databases that were set up this way, so I was wanting to do something similar to what I described above, and use an Array to cycle through each database, and of course the possibleFields in FieldsIndatabase would vary depending on what database I was connecting to, so that was the best solution I could come up with.

The Referee fields are the same for every database...that's why I was thinking to store all of the possibleFields in a variable and append the variable to my "Standard SQL Statement (Referee Fields)

Is it possible to do what I am wanting, or can someone come up with a better way to go about this? Please let me know if you need more information, or if I wasn't descriptive enough.

[snb]

To geneate an inventory of all fields:

[VBA]
Sub snb()
With New Recordset
.Open "SELECT * FROM TABEL1", "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=G:\Access\fiets.mdb"
For Each fld In .Fields
c01 = c01 & "_" & fld.Name
Next
End With

Cells(1).Resize(, UBound(Split(c01, "_"))) = Split(Mid(c01, 2), "_")
End Sub
[/VBA]

to retrieve a certain selection of fields:
[VBA]
Sub snb()
With New Recordset
.Open "SELECT " & Join(Array("address", "location", "email"), ",") & " FROM TABEL1", "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=G:\Access\fiets.mdb"
Sheets(1).Cells(20, 1).CopyFromRecordset .DataSource
End With
End Sub
[/VBA]

[richardSmith]

To geneate an inventory of all fields:

[vba]
Sub snb()
With New Recordset
.Open "SELECT " & Join(Array("address", "location", "email"), ",") & " FROM TABEL1", "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=G:\Access\fiets.mdb"
Sheets(1).Cells(20, 1).CopyFromRecordset .DataSource
End With
End Sub
[/vba]

This code looks like what I would want to accomplish. I am trying to break it down piece by piece to make sure I have a full understanding before I execute the code. I want to take the fieldNames that are housed in table1 and pull the data from those fields from table 2.

[richardSmith]

To geneate an inventory of all fields:

[vba]
Sub snb()
With New Recordset
.Open "SELECT " & Join(Array("address", "location", "email"), ",") & " FROM TABEL1", "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=G:\Access\fiets.mdb"
Sheets(1).Cells(20, 1).CopyFromRecordset .DataSource
End With
End Sub
[/vba]

This code looks like what I would want to accomplish. I am trying to break it down piece by piece to make sure I have a full understanding before I execute the code. I want to take the fieldNames that are housed in table1 and pull the data from those fields from table 2.

[snb]

Do table1 and table2 contain the same fields ?

[richardSmith]

In a sense yes. table1 contains the name of the fields. table2 contains the same fields but with data. So for example table1 would have fields, id, fieldNames. And of course id would be an autonumber and fieldNames would be PhoneNum, Address, EmployeeNumber etc. Then table2 would be EmployeeNumber, PhoneNumber, and Address.

Does that make sense? I uploded a sample database that contains a few junk entries to see if that will help shed light on it.

[richardSmith]

BUMP --- Any idea's guys?