ODBC to Access from Excel problem

[maxhayden]

I have never written SQL queries before – and I’m trying to integrate on into a bit of Excel VBA code:

So far this is successful:
[vba]
Sub FindaReference()
Dim varConnection
Dim varSQL
Cells(i,1). Select
varConnection = "ODBC; DSN=MS Access Database;DBQ=C:\Users\*xxxxx*\Documents\IDSDB.mdb; Driver={Driver do Microsoft Access (*.mdb)}"
varSQL = "SELECT [instructionsreceivedDS-090816].[Instruction Reference], [instructionsreceivedDS-090816].[Instruction Type], [instructionsreceivedDS-090816].[Instruction Received], [instructionsreceivedDS-090816].[BlahBlah], [instructionsreceivedDS-090816].[BlahBlah1], [instructionsreceivedDS-090816].[BlahBlah2], [instructionsreceivedDS-090816].[Instructor Name] FROM [instructionsreceivedDS-090816] WHERE [instructionsreceivedDS-090816].[Instruction Reference] = '" + TextBox1.Text + " ' "
With ActiveSheet.QueryTables.Add(Connection:=varConnection, Destination:=Cells(i, 1)
.CommandText = varSQL
.Name = "Query-39008"
.Refresh BackgroundQuery:=False
End With
End Sub
[/vba]

I want to type a reference number in Textbox1 – then the SQL query looks up the reference in the [Instruction Reference] field. Then pulls up the other fields for that query (BlahBlah1,2, etc) in a row.

The problem I'm getting is that it returns the column names regardless of what reference number I put in - or if I don't put one in at all!!

So I get back Instruction Reference, BlahBlah, BlahBlah1, etc... from the access database.

I've tried using TextBox1.Value. This doesn't make a difference. I've also tried removing the ' ' surrounding TextBox1 and a MisMatch error comes up.

I'm completely new to SQL queries so I don't know if the query is correct. (I made it in Query Wizard) - The first part works on its own - without the WHERE statement - and pulls all the data - but I just want 1 row![/font]

Thanks in advance for your help.

[Bob Phillips]

Try this

[vba]

Sub FindaReference()
Dim varConnection
Dim varSQL
Cells(i, 1).Select
varConnection = "ODBC; DSN=MS Access Database;" & _
"DBQ=C:\Users\*xxxxx*\Documents\IDSDB.mdb;" & _
"Driver={Driver do Microsoft Access (*.mdb)}"
varSQL = "SELECT [instructionsreceivedDS-090816].[Instruction Reference], " & _
" [instructionsreceivedDS-090816].[Instruction Type],, " & _
" [instructionsreceivedDS-090816].[Instruction Received],, " & _
" [instructionsreceivedDS-090816].[BlahBlah], " & _
" [instructionsreceivedDS-090816].[BlahBlah1], " & _
" [instructionsreceivedDS-090816].[BlahBlah2], " & _
" [instructionsreceivedDS-090816].[Instructor Name], " & _
"FROM [instructionsreceivedDS-090816], " & _
"WHERE [instructionsreceivedDS-090816].[Instruction Reference] = '" & TextBox1.Text & " ' "
With ActiveSheet.QueryTables.Add(Connection:=varConnection, Destination:=Cells(i, 1))
.CommandText = varSQL
.Name = "Query-39008"
.Refresh BackgroundQuery:=False
End With
End Sub
[/vba]

[maxhayden]

It doesn't appear to be working. It comes up with "General ODBC Error" and in Debug mode highlights the [VBA].Refresh BackgroundQuery:=False [/VBA] line.

[maxhayden]

I tried taking that line out and it does fill the fields with anything from the database - but an error message isn't coming up. Hmmmm

[maxhayden]

Problem solved!!!! The reference numbers had extra text on the end which I hadn't noticed. Apologies!

I have put a LEFT(xxxxx, 6) so it searches the first 6 numbers. That works fine now.


I do have one more question though.

Everytime I run the macro, it returns the headers in 1 row, and the values in the row below.

What can I do to make it just return the values?

Thanks in advance

[maxhayden]

I have asked the beholder of all knowledge (google) how to not import the column headers with eash query I run (in the code above) but there doesn't seem to be anything out there.

There must be a way to not import the headers with query.

I have had one idea (basically running a delete function in the i loop, where it deletes Rows(i-1) ) but that is pretty messy. I'd rather not import the header in the first place.

[Marcster]

There's a VBA function [Range].CopyFromRecordset
Which copies from an ADO recordset directly to Excel cell.
The headers do not appear.