Access query results in Excel with Captions

[vineet78]

Hi

Have a VBA code that imports data from access query to excel but without formatting.
I have given query fields names in in caption field but when i export it to excel, instead of caption names comes data fields names.

Have lot googled on this but no gain !!
have spent many hours on resolving this, any help would be very great.

I want to enhance VBA code so as to include this feature.\

Regards
Vineet

 

[xenou]

Hi, the query should use aliases in the sql.

For plain sql that is written as:

select Field1 as MyFieldName from Table1;

In the query designer it uses the syntax in the query builder (which is converted the same sql as above internally:

MyFieldName: Field1

Actual captions will only work in Access.

 

[vineet78]

thanks, but i understand that you said that this caption will only work in access.
How can i export query results in excel with captions? any chances please?

 

[vineet78]

this is the code i am using
Dim sSQLQry As String
Dim ReturnArray
Dim Conn As New ADODB.Connection
Dim mrs As New ADODB.Recordset
Dim DBPath As String, sconnect As String
Dim dbname As String
dbname = Sheets("Home").Range("C11").Value

Conn.Open "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & dbname & ";"
sconnect = "Provider=MSDASQL.1;DSN=Excel Files;DBQ=" & DBPath & ";HDR=Yes';"

'Clean sheet
Application.ScreenUpdating = True
Sheets("Operations-Data").Select
Range("A21:T60000").ClearContents
Sheets("Operations-Data").Range("A20:IV20").ClearContents
Select Case Sheets("Operations-Data").Cells(12, 3).Value
Case "Employee"
sSQLSting = "SELECT * FROM [Employee] ORDER BY Period"
Case "BAUcost"
sSQLSting = "SELECT * FROM [BAUcost] order by Period"
Case "ITcost"
sSQLSting = "SELECT * FROM [ITcost] ORDER BY Period"
Case "HR"
sSQLSting = "SELECT * FROM

---

"
End Select
mrs.Open sSQLSting, Conn
Sheets("Operations-Data").Range("B21").CopyFromRecordset mrs
col = 2
For Each CAMPO In mrs.Fields
Sheets("Operations-Data").Cells(20, col).Value = CAMPO.Name
col = col + 1
Next CAMPO
mrs.Close
Conn.Close

 

[xenou]

Hi,
You cannot use Select * FROM [Employee] as the sql. You must use explicit field names: Select MyField1, MyField2, MyField3 as MyField3OtherName from [Employee] The last field uses a column alias so it would be called MyField3OtherName instead of MyField3.

If you do use Select * .... then you will get the names as they exist in the database table.

 

[vineet78]

Thanksss !! a lot for this knowledge update. I will use it and let you know today.
However facing another issue today , please help

I am using a VBA code to extract results of Access query in excel.
Using below code for the query
sSQLSting = "SELECT * From [QTD] WHERE Region='" & Reg & "' AND [Zone]='" & zn & "' AND [Group]='" & grp & "' AND [Country]='" & cty & "'"
so above i am using 4 variables to query the access query.
But these are not working if either variable of them is blank.
Please advise how can i define query so that this works even if one or more than one of the variables is blank.

 

[xenou]

You can use like instead of bare equality to account for criteria that may or may not be filled in.

Example:

if len(reg) = 0 then reg = "*"
if len(zn)  = 0 then zn = "*"
if len(grp) = 0 then grp = "*"
if len(cty) = 0 then cty = "*"
sSQLSting = "SELECT * From [QTD] WHERE Region like '" & Reg & "' AND [Zone] like '" & zn & "' AND [Group] like '" & grp & "' AND [Country] = like '" & cty & "'"

 

[vineet78]

This is giving blank results !!
But when using this criteria in access database, it is fine

But i want to use this query through Excel vba

 

[vineet78]

Do you think the error can be due to defining these variables as string
Dim Reg, zn, grp, cty, ety As String

 

[vineet78]

Do you think the error can be due to defining these variables as string
Dim Reg, zn, grp, cty, ety As String

Thankssssss) i can find out the results now , actually for ADO it is % but not *...thanks again very much ))))