Cannot open SQL Server Database in Outlook VBA

[vodkasoda]

I am rewriting some programs of mine that I lost and am using a new combination of software : Windows 10, Visual Studio Community 2017, SQL Server Express 2017 and Office 2016 (I had the exact same problem with Office 2007).
I have created a SQL Server Project in Visual Studio, which seems to be OK, and I can see the Project in my data Folder. I can access and update the data using Visual Studio and SQL Server Management Studio 17.
However, when I try to Open the Database in my Outlook VBA program I get an error as follows ...

https://i.stack.imgur.com/ZeE9C.png (https://i.stack.imgur.com/ZeE9C.png)

This is my code, the Data Source details are all copied from the Target Connection String generated in Visual Studio, I added the Provider=SQLOLEDB line as that seemed to be what my searches online insinuated it should be, but I don't know ... I have found mention of problems like this needing a change in the registry, but the OLEDB_SERVICES entry already exists and if that is the case the website says "examine your connection string for other possible errors" ... well yes, but that's the problem isn't it ?!?!?

I just need to connect to the Database so I can get on with this :crying: !!!



Set KA_DB = New ADODB.Connection
Set KA_RS_Leagues = New ADODB.Recordset
Set KA_Com = New ADODB.Command

KA_DB.Open "Provider=SQLOLEDB;" & _
"Data Source=GARYSPC\SQLEXPRESS;" & _
"Initial Catalog=KADB;" & _
"Integrated Security=True;" & _
"Persist Security Info=False;" & _
"Pooling=False;" & _
"MultipleActiveResultSets=False;" & _
"Connect Timeout=60;" & _
"Encrypt=False;" & _
"TrustServerCertificate=True"

Set KA_Com.ActiveConnection = KA_DB


Please can somebody tell me what I am doing wrong ?!?

[vodkasoda]

Resolved ...

After dozens of trial & error guesswork this line worked :
KA_DB.Open "Provider=SQLOLEDB;Server=GARYSPC\SQLEXPRESS;Database=KADB; Trusted_Connection=yes"
It seems that the TrustedConnection=yes was the key ...

[leciafosl]

how did you fix it?

[HeitaWoorb]

That's a relatively common issue for my SQL server database. Every second time when I try to open this database in Outlook, it just doesn't work! Why? I still don't know. I have wanted to fix it so many times, and I still cannot find a proper solution for this very issue. For f sake, I had a couple of customers that actually refused the order as they weren't able to open that damn database in freaking Outlook! Since then, I have always tried to keep a copy database sql server in any other format on any other device.

[Maureene]

The same issue appeared with my database in January this year. Even though I'm a newbie to SQL databases, I've been trying to integrate Outlook with SQL all by myself. Although the code works perfectly, something tells me it's not efficient enough. I would like to represent it to the outlook experts . Who can guide me to learn the best way to connect, insert or update a database from Outlook VBA? Thanks in advance!

[SamT]

Maureen,

Due to the Age of t6his thread, I am closing it.

Please open a new thread; cite the particulars of your setup; then post your current code.

I am sure you will see many responses to the new thread.



@HeitaWoorb,
BOLO for Maureen's new thread.