Consulting

Results 1 to 6 of 6

Thread: Cannot open SQL Server Database in Outlook VBA

  1. #1
    VBAX Regular
    Joined
    Mar 2009
    Location
    Stowmarket
    Posts
    62
    Location

    Cannot open SQL Server Database in Outlook VBA

    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 ...



    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 !!!

    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 ?!?

  2. #2
    VBAX Regular
    Joined
    Mar 2009
    Location
    Stowmarket
    Posts
    62
    Location

    Resolved / Closed

    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 ...

  3. #3
    how did you fix it?

  4. #4
    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.

  5. #5
    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!

  6. #6
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    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.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •