connecting to SQL express through ADO
-
Hi all, I have SQL server express edition 2005 installed on my computer and using VB in MS Access to run some queries. The VB function looks something like this.. Dim dataRS As New ADODB.Recordset Dim cnnSQL As New ADODB.Connection Set cnnSQL = New ADODB.Connection cnnSQL.ConnectionString = "Provider=SQLNCLI;Server=.\SQLExpress;Database=TestDB;Trusted_Connection=yes;" cnnSQL.Properties("Integrated Security").Value = "SSPI" cnnSQL.Open dataRS.Open "SELECT dbo_Table_1.* FROM dbo_Table_1", cnnSQL, adOpenStatic, adLockOptimistic The code fails though, on the last line with message "Invalid object name 'dbo_Table_1'" (yes, I verified that the table actually does exist in the DB) I must be doing something wrong with the connection string but all the things that I've tried so far haven't worked.. Can anyone offer any suggestions? Thanks in advance,
-
Hi all, I have SQL server express edition 2005 installed on my computer and using VB in MS Access to run some queries. The VB function looks something like this.. Dim dataRS As New ADODB.Recordset Dim cnnSQL As New ADODB.Connection Set cnnSQL = New ADODB.Connection cnnSQL.ConnectionString = "Provider=SQLNCLI;Server=.\SQLExpress;Database=TestDB;Trusted_Connection=yes;" cnnSQL.Properties("Integrated Security").Value = "SSPI" cnnSQL.Open dataRS.Open "SELECT dbo_Table_1.* FROM dbo_Table_1", cnnSQL, adOpenStatic, adLockOptimistic The code fails though, on the last line with message "Invalid object name 'dbo_Table_1'" (yes, I verified that the table actually does exist in the DB) I must be doing something wrong with the connection string but all the things that I've tried so far haven't worked.. Can anyone offer any suggestions? Thanks in advance,
psasidisrcum wrote:
"SELECT dbo_Table_1.* FROM dbo_Table_1
I'm not exactly sure, but isn't it odd to SELECT Table1 FROM Table1? You also have a period after the first dbp_Table_1 which may be causing fits. Jerry
"When I get a little money, I buy books and if any is left, I buy food and clothes." --Erasmus
-
Hi all, I have SQL server express edition 2005 installed on my computer and using VB in MS Access to run some queries. The VB function looks something like this.. Dim dataRS As New ADODB.Recordset Dim cnnSQL As New ADODB.Connection Set cnnSQL = New ADODB.Connection cnnSQL.ConnectionString = "Provider=SQLNCLI;Server=.\SQLExpress;Database=TestDB;Trusted_Connection=yes;" cnnSQL.Properties("Integrated Security").Value = "SSPI" cnnSQL.Open dataRS.Open "SELECT dbo_Table_1.* FROM dbo_Table_1", cnnSQL, adOpenStatic, adLockOptimistic The code fails though, on the last line with message "Invalid object name 'dbo_Table_1'" (yes, I verified that the table actually does exist in the DB) I must be doing something wrong with the connection string but all the things that I've tried so far haven't worked.. Can anyone offer any suggestions? Thanks in advance,
psasidisrcum wrote:
Dim cnnSQL As New ADODB.Connection Set cnnSQL = New ADODB.Connection
Are you sure the root cause isn’t the double initialize of cnnSQL? Dim cnnSQL As ADODB.Connection Set cnnSQL = New ADODB.Connection
psasidisrcum wrote:
dataRS.Open "SELECT * FROM dbo_Table_1", cnnSQL, adOpenStatic, adLockOptimistic
I'd love to help, but unfortunatley I have prior commitments monitoring the length of my grass. :Andrew Bleakley:
-
psasidisrcum wrote:
"SELECT dbo_Table_1.* FROM dbo_Table_1
I'm not exactly sure, but isn't it odd to SELECT Table1 FROM Table1? You also have a period after the first dbp_Table_1 which may be causing fits. Jerry
"When I get a little money, I buy books and if any is left, I buy food and clothes." --Erasmus
Jerry Hammond wrote:
I'm not exactly sure, but isn't it odd to SELECT Table1 FROM Table1?
He isn't he's selecting
dbo_Table1.*which is a valid notation to mean all columns from dbo_Table1 - It is most commonly used when there are joined tables - It is redundant if there is only one table in the select becauseSELECT *would be just as effective.Jerry Hammond wrote:
You also have a period after the first dbp_Table_1 which may be causing fits.
The dot is to allow you to more fully qualify a name if there is some ambiguity. Here the notation is reduntant because there is no ambiguity. The full notation is: [server].[database].[owner].[table].[column]
Upcoming Scottish Developers events: * UK Security Evangelists On Tour (2nd November, Edinburgh) * Developer Day Scotland: are you interested in speaking or attending? My: Website | Blog
-
Hi all, I have SQL server express edition 2005 installed on my computer and using VB in MS Access to run some queries. The VB function looks something like this.. Dim dataRS As New ADODB.Recordset Dim cnnSQL As New ADODB.Connection Set cnnSQL = New ADODB.Connection cnnSQL.ConnectionString = "Provider=SQLNCLI;Server=.\SQLExpress;Database=TestDB;Trusted_Connection=yes;" cnnSQL.Properties("Integrated Security").Value = "SSPI" cnnSQL.Open dataRS.Open "SELECT dbo_Table_1.* FROM dbo_Table_1", cnnSQL, adOpenStatic, adLockOptimistic The code fails though, on the last line with message "Invalid object name 'dbo_Table_1'" (yes, I verified that the table actually does exist in the DB) I must be doing something wrong with the connection string but all the things that I've tried so far haven't worked.. Can anyone offer any suggestions? Thanks in advance,
psasidisrcum wrote:
The code fails though, on the last line with message "Invalid object name 'dbo_Table_1'"
Are you sure you really have a table name called dbo_Table_1? Are you sure it isn't dbo.Table_1? (i.e. there is a dot between dbo and Table_1 rather than an underscore)
Upcoming Scottish Developers events: * UK Security Evangelists On Tour (2nd November, Edinburgh) * Developer Day Scotland: are you interested in speaking or attending? My: Website | Blog
-
psasidisrcum wrote:
Dim cnnSQL As New ADODB.Connection Set cnnSQL = New ADODB.Connection
Are you sure the root cause isn’t the double initialize of cnnSQL? Dim cnnSQL As ADODB.Connection Set cnnSQL = New ADODB.Connection
psasidisrcum wrote:
dataRS.Open "SELECT * FROM dbo_Table_1", cnnSQL, adOpenStatic, adLockOptimistic
I'd love to help, but unfortunatley I have prior commitments monitoring the length of my grass. :Andrew Bleakley:
S Douglas wrote:
Are you sure the root cause isn’t the double initialize of cnnSQL?
While the double initialisation is redundant it is unlikely to be the cause of the problem because the second connection is constructed over the first before anything is done with the first connection.
Upcoming Scottish Developers events: * UK Security Evangelists On Tour (2nd November, Edinburgh) * Developer Day Scotland: are you interested in speaking or attending? My: Website | Blog
-
Jerry Hammond wrote:
I'm not exactly sure, but isn't it odd to SELECT Table1 FROM Table1?
He isn't he's selecting
dbo_Table1.*which is a valid notation to mean all columns from dbo_Table1 - It is most commonly used when there are joined tables - It is redundant if there is only one table in the select becauseSELECT *would be just as effective.Jerry Hammond wrote:
You also have a period after the first dbp_Table_1 which may be causing fits.
The dot is to allow you to more fully qualify a name if there is some ambiguity. Here the notation is reduntant because there is no ambiguity. The full notation is: [server].[database].[owner].[table].[column]
Upcoming Scottish Developers events: * UK Security Evangelists On Tour (2nd November, Edinburgh) * Developer Day Scotland: are you interested in speaking or attending? My: Website | Blog
Colin Angus Mackay wrote:
He isn't he's selecting dbo_Table1.* which is a valid notation to mean all columns from dbo_Table1 - It is most commonly used when there are joined tables - It is redundant if there is only one table in the select because SELECT * would be just as effective.
Cool. That is good to know. He does say, "yes, I verified that the table actually does exist in the DB)".
Colin Angus Mackay wrote:
The dot is to allow you to more fully qualify a name if there is some ambiguity. Here the notation is reduntant because there is no ambiguity. The full notation is: [server].[database].[owner].[table].[column]
Right. That would mean--I assume--that when he writes
dbo_Table_1.he is selecting his Server?"When I get a little money, I buy books and if any is left, I buy food and clothes." --Erasmus
-
Colin Angus Mackay wrote:
He isn't he's selecting dbo_Table1.* which is a valid notation to mean all columns from dbo_Table1 - It is most commonly used when there are joined tables - It is redundant if there is only one table in the select because SELECT * would be just as effective.
Cool. That is good to know. He does say, "yes, I verified that the table actually does exist in the DB)".
Colin Angus Mackay wrote:
The dot is to allow you to more fully qualify a name if there is some ambiguity. Here the notation is reduntant because there is no ambiguity. The full notation is: [server].[database].[owner].[table].[column]
Right. That would mean--I assume--that when he writes
dbo_Table_1.he is selecting his Server?"When I get a little money, I buy books and if any is left, I buy food and clothes." --Erasmus
Jerry Hammond wrote:
Right. That would mean--I assume--that when he writes dbo_Table_1. he is selecting his Server?
No, it goes the other way around. The column is the mandatory bit, so it will parse as if the colum is the last thing and work back from there. Valid sequences, when expecting a column name, are therefore: [server].[database].[owner].[table].[column] [database].[owner].[table].[column] [owner].[table].[column] [table].[column] [column] Valid sequences, when expecting a table name, are: [server].[database].[owner].[table] [database].[owner].[table] [owner].[table] [table] (Note the owner name, in both cases, can be omitted when it is the dbo, so you'd get the two dots with nothing inbetween. e.g. mydatabase..mytable)
Upcoming Scottish Developers events: * UK Security Evangelists On Tour (2nd November, Edinburgh) * Developer Day Scotland: are you interested in speaking or attending? My: Website | Blog
-
Jerry Hammond wrote:
Right. That would mean--I assume--that when he writes dbo_Table_1. he is selecting his Server?
No, it goes the other way around. The column is the mandatory bit, so it will parse as if the colum is the last thing and work back from there. Valid sequences, when expecting a column name, are therefore: [server].[database].[owner].[table].[column] [database].[owner].[table].[column] [owner].[table].[column] [table].[column] [column] Valid sequences, when expecting a table name, are: [server].[database].[owner].[table] [database].[owner].[table] [owner].[table] [table] (Note the owner name, in both cases, can be omitted when it is the dbo, so you'd get the two dots with nothing inbetween. e.g. mydatabase..mytable)
Upcoming Scottish Developers events: * UK Security Evangelists On Tour (2nd November, Edinburgh) * Developer Day Scotland: are you interested in speaking or attending? My: Website | Blog
Cool beans, Colin. Thanks for clearing that up for me.
"When I get a little money, I buy books and if any is left, I buy food and clothes." --Erasmus
-
S Douglas wrote:
Are you sure the root cause isn’t the double initialize of cnnSQL?
While the double initialisation is redundant it is unlikely to be the cause of the problem because the second connection is constructed over the first before anything is done with the first connection.
Upcoming Scottish Developers events: * UK Security Evangelists On Tour (2nd November, Edinburgh) * Developer Day Scotland: are you interested in speaking or attending? My: Website | Blog
Colin Angus Mackay wrote:
it is unlikely to be the cause of the problem because the second connection is constructed over the first before anything is done with the first connection.
CP choked on my post and I was way to lazy (well tired) to retype it all out again. I have had issues with simple errors of that nature. Nothing big just flaky behavior by the object.
I'd love to help, but unfortunatley I have prior commitments monitoring the length of my grass. :Andrew Bleakley: