Excel VBA Userform to SQLOLEDB Connection works Locally but not remotely - Run-time error '-2147467259 (80004005)': [DBNETLIB]

Question

It's been a week of research now and I am still unable to fix this problem. I have created an Excel VBA User form that inserts data into an SQLOLEDB Connection (SQLEXPRESS 2017) when a CommandButton (Called "Save") is clicked. Locally (localhost) it works like a charm. All of the data from the columns gets inserted into the SQL DB Table successfully. No errors whatsoever.

However, when any client attempts to perform the same action I get the Run-time error '-2147467259 (80004005)': [DBNETLIB](ConnectionOpen (connect()).]SQL Server does not exist or access denied.

This is what I have tried so far:

• I have created a Login for the client in my SQL Server Management Studio.
• I have granted Insert permissions to the client to the dbo.Table.
• I have made sure "Allow Remote Connections to this Server" is checked in Connections in my server properties in SQL Server Management Studio.
• I have added Inbound & Outbound Rules in my Windows Firewall to Open port 1433 in my Computer which is where the Server has been installed.
• I have added Inbound & Outbound Rules in Windows Firewall to Open port 1433 in my client's Computer.
• I have checked my ODBC Data Source Administrator (both 32 & 64-bit) and I found out I have SQL Server Native Client RDA 11.0 installed which my client does not have. I researched and it's no longer possible to install this version. I also found out about SQL Server Compact 3.5 which supposedly comes with a replacement to RDA called Synchronization Services for ADO.NET. I installed both 32 & 64-bit versions in both my computer and my clients.
• I have also tried establishing a Remote Desktop connection with my own login info but when I test my user form or run my Tester. UDL to attempt to connect to this server I get the same error message.

This is the string I'm using to establish this connection:

conn.Open "Provider=SQLOLEDB;Data Source=PCNAME\SQLEXPRESS;Initial Catalog=DATABASENAME;Integrated Security=SSPI;Trusted_Connection=Yes"

conn.Execute "INSERT INTO dbo.TABLENAME (COLUMNNAME, COLUMNNAME2) values ('" & sColumnVariable & "', '" & sColumnVariable2 & "')"

My question will be:

How do I manage to allow clients to successfully insert into this SQL Database/Table?

Where does the problem lie? Does it lie within my Computers Settings? Does it lie within my Clients Computers Settings? Does it lie within my SQL Server Management Studio (maybe wrong/missing permissions)?

I appreciate your help in advance! Regards, Dave.

Answer 1

I was able to figure this out. It had to do with the IP. I used my Local Area Connection IP address ONLY, without \SQLEXPRESS along with a new user I created in SQL Server Management Studio in my connection string:

conn.Open "Provider=SQLOLEDB;Data Source=IPADDRESS;Initial Catalog=DATABASENAME;Integrated Security=SSPI;User ID=MySQLTest;password=myPassc0de;

I'm soooo happy.

Answer 2

You have to use the IP address to connect, for example:

  Data Source=101.101.101.101\SQLEXPRESS;

1. Open a DOS prompt on the machine where SQL Server Express is located and type ipconfig
2. Get IP address and use it in your connection string

Answer 3

LocalHost just refers you your own computer. So unless you have a SQL Express running on each of the client computers, it isn't going to work.

You need to use the proper computer name or an IP address.

Is SQL Express running on a Windows Server?

I'm pretty sure that this is a requirement if you want other computers to talk to your SQL Server Express database. It's fine to use to test locally, but you need to install SQL Server on an PC that is actually running Windows Server to be able to accept and route the connections properly. So while SQL Server Express if free, your going to have to pay for Windows Server if you don't already have one.

If not, I'd love to know to get around dealing with my IT department so I can get something done.