0

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.

Jonny
  • 1,319
  • 1
  • 14
  • 26
Dave
  • 19
  • 1
  • 6
  • Does this relate? https://www.sqlservercentral.com/Forums/Topic301766-20-1.aspx If yes, indicates software update needed. – QHarr Feb 23 '18 at 18:09
  • 1
    Thanks QHarr. 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; – Dave Feb 24 '18 at 02:39

3 Answers3

0

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
  • Thank you!!!! I was able to figure this out. And yes. It had to do with the IP. I used the IP address ONLY, without \SQLEXPRESS and it worked. Thank you very much! – Dave Feb 24 '18 at 02:35
0

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.

Profex
  • 1,370
  • 8
  • 20
  • Thank you very much Profex! 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; – Dave Feb 24 '18 at 02:38
  • @Dave Good to hear it's working for you. What version of Windows is SQL Server running on? – Profex Feb 26 '18 at 19:44
  • It's currently running on Windows 10. – Dave Feb 27 '18 at 20:36
0

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.

Dave
  • 19
  • 1
  • 6
  • I got it to work (so far), without changing my firewall, by just specifying a Port in the connection string `Provider=SQLOLEDB;Server=tcp:myComputer,49173;Database=myDatabase;Integrated Security=SSPI;` BTW, since you have `Integrated Security=SSPI`, it means that you are using your domain account to login (the User ID & Password are ignored). – Profex Mar 28 '18 at 17:31