Cursor not declared when trying to connect

We have an application that accesses Axapta SQL  databases. Our sql code works correctly and accesses one customer's database, but using the same code to access
another customer's database yields the following error message:  

Error in fOpenDB = 37000: [Microsoft][ODBC Sql Server Driver][Sql Server] The cursor was not declared - err.Number = 40002

We are coding with VB6 and are using Sql Server 2000 on a Windows 2000 machine.
Any help would be appreciated.
MGC22Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

CJ_SCommented:
Where did you declare your cursor? on the server or the client? Note that you cannot use both cursor (i.e. in VB and in SQL Server).
MGC22Author Commented:
I was not declaring a cursor in VB. I have since added the following code(see below) and the declare cursor error is corrected but now I receive the following error on both databases.

Error Message:   Error in fOpenExpOrd = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'CustTable' err.Number = -2147217865

Code:
oConn.Open "Driver={SQL Server};" & _
           "Server=MyServerName;" & _
           "Database=myDatabaseName;" & _
           "Uid=myUsername;" & _
           "Pwd=myPassword"

     Set rsADOAXOrdHdr = New ADODB.Recordset
     rsADOAXOrdHdr.CursorType = adOpenKeyset
     rsADOAXOrdHdr.LockType = adLockOptimistic
     rsADOAXOrdHdr.Open "CustTable", oConn, , , adCmdTable

Error occurs on the open, Table name is correct.

Thanks for any help
MGC22Author Commented:
I have just noticed something else, for the database that was working the table owner is 'dbo'. For the database that is not working the owner is 'bmssa'. The not working database has a table that the owner is 'dbo' and I can gain access to it!
So I guess my new question is how do I gain access to the tables that  have the owner 'bmssa'? Will it be posible to acces both database's tables with 1 set of code?

Thanks
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

felipe_51Commented:
Hi!
  Try adding or creating a new database roles to assing
who-can-access-what on the database or simply try modifying the role 'public'.

--==============================
--To create a new server role named Managers
--==============================
sp_addrole 'Managers'

--==================================
--To add a security account to an existent server role
--==================================
      sp_addrolemember
            @rolename = 'Managers' ,
            @membername = 'existing_security_account'

modifiying the public role would be suitable to gant 'Options'
like 'SELECT','UPDATE','DELETE','EXECUTE'... to dessired tables
and StoredProc, you want every user have access.

Regards, Felipe
felipe_51Commented:
With the previously comment stated In the previous example you could try this to add bmssa to the owner server role:

      sp_adduser
             @loginame =  'bmssa',
             @name_in_db =  'bmssa',
             @grpname =  'db_owner'
CJ_SCommented:
Yep, or you go to the enterprise manager -> sql server -> security -> logins. And to the database access tab inside the users properties.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MGC22Author Commented:
Thanks for all your help.
Since the Server and database are the customers system and we just access it for verifying information and some updating I have created a variable that I save in the registry as to who the owner of the table is and then use that information when querying the data.
felipe_51Commented:
_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/
I'm Glad that you finally find a solution.

But I still wonder why... of your accepted Answer!

Best Regards, Felipe
_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.