accessing a SQL View from VBA

[thekind78]

Hello,
I am trying to access the results of a sql view from VBA. When trying to query the results of the view through VBA I get a "Object not found" error. I'm able to query tables, but have difficulty accessing views. Not well versed in SQL but I understand that Views seem to be virtual tables, however I am not sure why I am unable to query. Any suggestions much appreciated. Thanks in advance.

[fplesco]

Hello,
I am trying to access the results of a sql view from VBA. When trying to query the results of the view through VBA I get a "Object not found" error. I'm able to query tables, but have difficulty accessing views. Not well versed in SQL but I understand that Views seem to be virtual tables, however I am not sure why I am unable to query. Any suggestions much appreciated. Thanks in advance.

Hey -

For us to get a clearer picture of your problem, you can post part of your code here.

[thekind78]

Here is what I'm trying to do in VBA:

query = "Select * from h8888.MaxRate"

Set ado_conn = New ADODB.Connectio n
ado_conn.Connec tionString = "ODBC;DRIVER=SQ L Server;" & "DSN=" & dsn & ";" & Db & ";" & uid
ado_conn.Open
Set ado_rs = New ADODB.Recordset


Set xlSht = ThisWorkbook.Sh eets("Rawdata")
xlSht.Range("A1 :H6000").ClearC ontents
ado_rs.Open query, ado_conn
xlSht.Range("A1 ").CopyFromReco rdset ado_rs
ado_rs.Close
ado_conn.Close
Set ado_rs = Nothing
Set ado_conn = Nothing

The code fails when trying to send the query to the server. It receives an "Invalid Object Name" regarding the "h8888.MaxR ate" table I am trying to access. This table is actually a view. I am not sure as to the exact specifics between a view and a table, however, the code above works perfectly for tables. Any thoughts? Much appreciated.

[fplesco]

Here is what I'm trying to do in VBA:

query = "Select * from h8888.MaxRate"

Set ado_conn = New ADODB.Connectio n
ado_conn.Connec tionString = "ODBC;DRIVER=SQ L Server;" & "DSN=" & dsn & ";" & Db & ";" & uid
ado_conn.Open
Set ado_rs = New ADODB.Recordset


Set xlSht = ThisWorkbook.Sh eets("Rawdata")
xlSht.Range("A1 :H6000").ClearC ontents
ado_rs.Open query, ado_conn
xlSht.Range("A1 ").CopyFromReco rdset ado_rs
ado_rs.Close
ado_conn.Close
Set ado_rs = Nothing
Set ado_conn = Nothing

The code fails when trying to send the query to the server. It receives an "Invalid Object Name" regarding the "h8888.MaxR ate" table I am trying to access. This table is actually a view. I am not sure as to the exact specifics between a view and a table, however, the code above works perfectly for tables. Any thoughts? Much appreciated.

You may try creating a view without a dot or try using one word VIEW name.