Accessing Remote DB with VB6

I have a data-driven vb6 app that uses a sql server express 2008.  It runs fine off of the sql server on the machine itself.  Is there anyway I can get it to "find it" from my server if I can see the server-located database in my local sql server console?  I would really like to run off the server so I don't have to manually move data back-and-forth.
Bob SchneiderCo-OwnerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Hi BobbaBuoy,

to connect to another server, you have to change connection string used in your application. Do you have source code for this application? Maybe connection string for this application can be configured (in config file, registry etc.)?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Don't forget to also allow remote connections to the SQL server from both its host server firewall and from the SQL configuration itself.

Go to a remote PC and go to Control Panel > Admin Tools > ODBC. Try to test a connection by making an ODBC. If this doesn't work, you may find that the SQL server is not configured to allow remote connections (the *default* state of SQL Express is to deny remote connections, so if you don't do this, you will not be connecting to your SQL server from a remote PC any time soon).

To allow remote connections to the server:
1. Open SQL Server Configuration Manager. Click Start -> Programs -> Microsoft SQL Server 2008 -> Configuration Tools -> SQL Server Configuration Manager.
2. On SQL Server Configuration Manager, select SQL Server Services on the left window. (SQL Browser service needs to be running btw).
3. On the left window, expand SQL Server Network Configuration -> Protocols for SQLEXPRESS. You see that TCP/IP protocol status is disabled.
4. Right-click on TCP/IP and select Enable to enable the protocol. Apply.
5. On the left window, select SQL Server Services. Select SQL Server (SQLEXPRESS) on the right window -> click Restart. The SQL Server service will be restarted.
6. You should also take note of the ports that are in use her (TCP port) and enable them in your firewall.

As Rimvis says above, you will need to ensure your connection strings are appropriate, as well. For example, don't use ./SQLEXPRESS as this implies localhost. Use SERVERNAME\SQLExpress as the instance name.

Hope that helps.
Bob SchneiderCo-OwnerAuthor Commented:
Thanks.  In response to HostOne, I think I am good there because I can access my remote sql server on my local machine using my sql server management studio.  Here is my connection string:

If sSource = "" Then
        conn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=CCMeet;UID=some_id;Pwd=password;Data Source=" & sSource
        conn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=CCMeet;UID=some_id;Pwd=password;Data Source=" & sSource
 End If

(Note that the "Else" clause is how I connect to the db locally and the other is how I am trying to connect to it remotely.  How can I configure the remote connection string using config file, registry, etc?
Hi BobbaBuoy,

"" does not look like valid host name to me. What error are you getting when connecting to remote server?
If you are able to connect to remote server by using some administration program (SQL Server Management Studio for example), use same host name in you connection string.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.