• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 499
  • Last Modified:

VB Error # -2147467259

Hi
I'm connecting to SQL Server using Excel.

Sometimes I get the message

VB Error # -2147467259
Generated by Microsoft OLE DB Provider
Cannot open database '.....' required by the login. Login failed

I get this message even though I don't change credentitals, Excel just decides to not work.

When I get this message, when opening up SQL Serve Management studio, the database is visible but doesn't have a '+' next to it, so can't see tables etc.

A reboot fixes things....

Help please for why this is occuring and how I can avoid having to always reboot, thanks
0
rwallacej
Asked:
rwallacej
  • 5
  • 2
1 Solution
 
rwallacejAuthor Commented:
Its Excel 2002 I'm running
The conncetion string is like this:

 "Provider=SQLOLEDB;Data Source=.\SQLEXPRESS;Initial Catalog=xxxxx;Integrated Security=SSPI;"
0
 
Ashish PatelCommented:
so you are using Windows Authenticated user to connect. Please check the login user has rights to connect to ther sql server.
0
 
rwallacejAuthor Commented:
using Windows Authenticated user to connect: that is correct

login user has rights to connect to ther sql server:  the same user can sometimes log-in; another time they can't, just minutes afterward
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Ashish PatelCommented:
What is your maximum concurrent user connection set to?
Look In Enterprise Manager, right click the server properties->connection tab. Let me know, if is it set to 0 (unlimited) or something else?
0
 
rwallacejAuthor Commented:
it is set to zero
0
 
rwallacejAuthor Commented:
any more ideas?

AFTER I can no longer connect from VBA in  Management studio when I try to connect to database I get message

Error Database cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server error log for details
the files aren't read only

I can see the database but no '+' next to it like the others (or like it after a reboot/during when macros are going fine)

thanks
0
 
assystCommented:
I would suggest to try using a sql server login by creating a new login in SQL Server and setting the SQL Server to mixed mode authentication. If this login works without any issues then the problem could be narrowed to windows authentication.
If you use windows authentication and password policy is set to change the password very often then you will not be able to connect to the SQL Server after changing your windows password. You need to update the password in SQl Server to get connected. Although your problem doesn't seem to be that but I thought this information could be helpful.
Let me know if you manage to create SQL login and test it.
0
 
rwallacejAuthor Commented:
thank you  assyst, issue is resolved
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now