By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,658 Members | 1,636 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,658 IT Pros & Developers. It's quick & easy.

retrieving query results from SQL server

P: 1

I'm currently trying to obtain the max(id) from a table in SQL Server using VBA so I can insert a new record with max(id) + 1.

I'm having trouble when I store the max id, I think it is a reference issue and Access does not see the column name.

Here is my code:
Expand|Select|Wrap|Line Numbers
  1. Set Cn = New ADODB.Connection
  2. Cn.Open "Driver={SQL Server};Server=" & Server_Name & ";Database=" & Database_Name & _
  3. ";Uid=" & User_ID & ";Pwd=" & Password & ";"
  5. SQLStr = "SELECT max(id) as max_id from tb_example"
  7. Set rs = Cn.Execute(SQLStr)
  8. matter_id = rs.Fields(max_id) -- FAILS HERE, says it does not exist
  9. Cn.Close
If I do 'select id from tb_example where id = 1', I can successfully reference it in rs.Fields(id). But right now I can't get it to work with an aggregate function in the SQL query. I've tried aliasing by using:

Expand|Select|Wrap|Line Numbers
  1. max(id) as max_id
  2. max(id) as "ID"
  3. max(id) max_id

all failed when referenced in the Fields part.

Any help would be much appreciated. Thanks in advance.
Nov 9 '11 #1
Share this Question
Share on Google+
1 Reply

P: 393
if it says the fields doesn't exist then do this:

Expand|Select|Wrap|Line Numbers
  1. matter_id = rs.Fields("max_id")
Expand|Select|Wrap|Line Numbers
  1. matter_id = rs.Fields(0)
Nov 9 '11 #2

Post your reply

Sign in to post your reply or Sign up for a free account.