CODEWITHSUNDEEP
sqlvbaexcel
Tomdw94
Tomdw94

Reputation: 37

Store a single value in variable with SQL Select

I have the following vba code. I am using MS Excel 2013. (I have an insert statement in this code as well, but I didn't copy this just to have a better highlight of my problem) 

Dim cn As Object
Dim cUserEmail As String
Dim strSqlmail As String
Dim rss As Object
Dim strConnection As String

Set cn = CreateObject("ADODB.Connection")
strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    "Data Source=C:\ActionList.accdb"

strSqlmail = "SELECT [Email-Adress] FROM MyTable WHERE Name='Test'"

Set rss = cn.Execute(strSqlmail)
cUserEmail = rss.Fields(0).Name

At this point it stores the column name (Email-Adress) in my variable. Running the query in Access returns the column with only my desired value in it.

I think I'm as good as finished, but I cannot seem to find how to get the value. When i change to rss.Field(1) it gives an error saying that the item cannot be found.

Upvotes: 0

Views: 5494

Answers (2)

Tomdw94
Tomdw94

Reputation: 37

The following code gave me the requested value: 

Dim cn As Object
Dim cUserEmail As String
Dim strSqlmail As String
Dim rss As Object
Dim strConnection As String

Set cn = CreateObject("ADODB.Connection")
strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    "Data Source=C:\ActionList.accdb"

strSqlmail = "SELECT [Email-Adress] FROM MyTable WHERE MyTable.[Name]='Test'"

Set rss = cn.Execute(strSqlmail)
cUserEmail = rss.Fields(0)

Upvotes: 0

Robin Mackenzie
Robin Mackenzie

Reputation: 19319

If you are using the ADO library then the Execute method returns a RecordSet - see the documentation.

So you need to:

Dim rsResult as Recordset
...
Set rsResult = cn.Execute(strSqlmail)
Do While Not rs.EOF
    Debug.Print rs!Email-Adress
    rs.MoveNext
Loop
rs.Close

So, when you add your WHERE clause to only return one row, then the loop should only iterate once. Whatever is output to the immediate window is what you should assign to cUserEmail.

Upvotes: 2

Related Questions