maxhayden
09-07-2009, 04:35 AM
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:
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
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.:doh:
So far this is successful:
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
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.:doh: