Error on query execution in VBA Excel using ADODB on SQL Server

We're trying to upsize from MS Jet (Access) db to SQL Server and am having a
major problem. Because we've not seen this error with the MS Jet db, I
suspect it's related to the user configuration in SQL Server.
The servername is "sqlbox" and the database name is "TCR". We use WinNT
authentication on the db and have a user profile for all our domain users.
The connection string executes, but the query does not. If the VBA Project
is unlocked, an error stating "Application Error...Unspecified Error" occurs
at the line noted below (--> ). When the VBA Project is locked (normally),
the error states "Invalid object name" and then lists the database table (in
this example, "Version").
Here's the pertinent code:
strConn = "Provider=SQLOLEDB;Data Source=sqlbox;" & _
"Persist Security Info=False;Initial Catalog=TCR;" & _
"Integrated Security=SSPI"
Set adoConn = New ADODB.Connection
adoConn.ConnectionString = strConn
adoConn.Open
strSQL = "SELECT * from Version WHERE Version.Filename = '" & strFname & "';
"
' open a recordset from Version table
Set adoRec = New ADODB.Recordset
adoRec.CursorLocation = adUseClient
--> adoRec.Open strSQL, adoConn <-- Error occurs here
I can run the application without problems; perhaps it is because I am the
dbowner (which implicitly grants me full access to the tables). However, I
have granted full access to the Domain User profile without success by any
other user.
Unless I find a way to solve this, there will be no migration! Thanks in
advance!Hi Glenn
You posted in the wrong place. There is a dedicated new group forACCESS/SQL
SERVER/ADP Integration. You will get better answers there as they are
specialized in that.
Regards
R.D
"Glenn Ray" wrote:

> We're trying to upsize from MS Jet (Access) db to SQL Server and am having
a
> major problem. Because we've not seen this error with the MS Jet db, I
> suspect it's related to the user configuration in SQL Server.
> The servername is "sqlbox" and the database name is "TCR". We use WinNT
> authentication on the db and have a user profile for all our domain users.
> The connection string executes, but the query does not. If the VBA Projec
t
> is unlocked, an error stating "Application Error...Unspecified Error" occu
rs
> at the line noted below (--> ). When the VBA Project is locked (normally)
,
> the error states "Invalid object name" and then lists the database table (
in
> this example, "Version").
> Here's the pertinent code:
> strConn = "Provider=SQLOLEDB;Data Source=sqlbox;" & _
> "Persist Security Info=False;Initial Catalog=TCR;" & _
> "Integrated Security=SSPI"
> Set adoConn = New ADODB.Connection
> adoConn.ConnectionString = strConn
> adoConn.Open
> strSQL = "SELECT * from Version WHERE Version.Filename = '" & strFname & "
';"
> ' open a recordset from Version table
> Set adoRec = New ADODB.Recordset
> adoRec.CursorLocation = adUseClient
> --> adoRec.Open strSQL, adoConn <-- Error occurs here
> I can run the application without problems; perhaps it is because I am the
> dbowner (which implicitly grants me full access to the tables). However,
I
> have granted full access to the Domain User profile without success by any
> other user.
> Unless I find a way to solve this, there will be no migration! Thanks in
> advance!

Error Message with Recordset.Update

Hi,

I am converting a VB6 application to C# using ADO. I have the following code:

private ADODB.Connection cn = new ADODB.Connection();
private ADODB.Recordset rs = new ADODB.Recordset();

private connectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data source= " + stdDir;
strClientSQL = "SELECT * FROM Client ORDER BY casename, renldate, product, fundingcode";

cn.CursorLocation = ADODB.CursorLocationEnum.adUseClient;
cn.Open(Utilities.connectionString,null,null,0);

rs.CursorLocation = ADODB.CursorLocationEnum.adUseServer;
rs.Open(strClientSQL,cn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic,-1);

rs.Fields["CaseName"].Value = this.txtCaseName.Text;

rs.Fields["CaseNo"].Value = this.txtCaseNumber.Text;

rs.Update();

When I try to compile and run it I get the following error message:

"No overload for method 'Update' takes '0' arguments"

I am not sure if I need to enter information in the update command or do something else. This seems pretty straight forward but I can't figure out how to do it. Can someone please help me?

Thanks,

Mark

The thing is that you have to place the fieldas and its values in the function, like this:

rsMsg.Update("Field1,Field2,Field3", "Value1,Value2,Value3");

But i supossed that you want to update the whole recordset, am i rigth?
That's a good question.... give me some time to answer that.

Hugo|||try without the parenthesis

rs.Update

Error Message with Recordset.Update

Hi,

I am converting a VB6 application to C# using ADO. I have the following code:

private ADODB.Connection cn = new ADODB.Connection();
private ADODB.Recordset rs = new ADODB.Recordset();

private connectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data source= " + stdDir;
strClientSQL = "SELECT * FROM Client ORDER BY casename, renldate, product, fundingcode";

cn.CursorLocation = ADODB.CursorLocationEnum.adUseClient;
cn.Open(Utilities.connectionString,null,null,0);

rs.CursorLocation = ADODB.CursorLocationEnum.adUseServer;
rs.Open(strClientSQL,cn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic,-1);

rs.Fields["CaseName"].Value = this.txtCaseName.Text;

rs.Fields["CaseNo"].Value = this.txtCaseNumber.Text;

rs.Update();

When I try to compile and run it I get the following error message:

"No overload for method 'Update' takes '0' arguments"

I am not sure if I need to enter information in the update command or do something else. This seems pretty straight forward but I can't figure out how to do it. Can someone please help me?

Thanks,

Mark

The thing is that you have to place the fieldas and its values in the function, like this:

rsMsg.Update("Field1,Field2,Field3", "Value1,Value2,Value3");

But i supossed that you want to update the whole recordset, am i rigth?
That's a good question.... give me some time to answer that.

Hugo|||try without the parenthesis

rs.Update