Problem creating ODBC connection to SQL SERVER EXPRESS (1 Viewer)

Plem · Jan 3, 2020

Hi,
I'm trying to setup an ODBC connection between my Access database and the SQL SERVER EXPRESS back end database on a new server, but cannot get the ODBC connection on the PC to find the SQL instance. I've done this a few times in the past but for the life of me cant see what i'm missing. Does anyone know of some step-by-step instructions.:banghead:

Many Thanks

theDBguy · Jan 3, 2020

Hi. Welcome to AWF!

Just to clarify, are you saying you moved the BE to a new machine? Or, are you saying you are trying to connect a new FE on a different machine to the same server you had before? I am asking because maybe it's just a matter of having the correct ODBC driver installed.

Plem · Jan 3, 2020

Hi, thanks for the reply. Its a new server i have had to setup with a new installation of SQL SERVER EXPRESS. I have gone through enabling TCP IP, enabling ports, running SQL BRowser service, etc... but must have missed something. i turned the server firewall off while i'm setting up but still cant get the ODBC (32 bit, 64 bit, File DSN, SYSTEM DSN) to show the SQL SERVER INSTANCE in the dropdown box.

theDBguy · Jan 3, 2020

Plem said:
Hi, thanks for the reply. Its a new server i have had to setup with a new installation of SQL SERVER EXPRESS. I have gone through enabling TCP IP, enabling ports, running SQL BRowser service, etc... but must have missed something. i turned the server firewall off while i'm setting up but still cant get the ODBC (32 bit, 64 bit, File DSN, SYSTEM DSN) to show the SQL SERVER INSTANCE in the dropdown box.

Okay, so even if you can't readily see the server instance from the dropdown, are you able to manually type in the server and instance name to connect to it?

Plem · Jan 3, 2020

Hi, On the server i can connect everything ok. I havent tried any other connections from the client PC if thats what you mean?

theDBguy · Jan 3, 2020

Plem said:
Hi, On the server i can connect everything ok. I havent tried any other connections from the client PC if thats what you mean?

Hi. Yes, that's what I meant, which was the question I was trying to clarify at the beginning. As in, "where" are you trying to connect from and to. So, if the server and client machines are connected on the same network and you can connect to the SQL Server instance from the server itself, then provided you have the correct ODBC driver installed on the client machine, you should be able to connect to the server from the client as well. Give it a try and let us know what happens. Good luck!

Plem · Jan 3, 2020

Hi, I am trying to setup an ODBC connection on the client PC but cannot see the SQL SERVER INSTANCE in the ODBC dropdown box.

theDBguy · Jan 3, 2020

Plem said:
Hi, I am trying to setup an ODBC connection on the client PC but cannot see the SQL SERVER INSTANCE in the ODBC dropdown box.

Hi. Okay, thanks for clarifying. Two things:

1. Do you have the correct ODBC driver installed on the client, and
2. Can you type in the server and instance name manually to connect to it?

Plem · Jan 3, 2020

Hi I am using the ODBC drivers that come with windows 10 Pro which normally work fine. How do i connect manually to the instance from the client?

theDBguy · Jan 3, 2020

Plem said:
Hi I am using the ODBC drivers that come with windows 10 Pro which normally work fine. How do i connect manually to the instance from the client?

Well, I could be wrong, but depending on the version of SQL Server Express you installed on the server, you might have to download and install a matching ODBC driver for it, in case what came with Windows is not compatible with it.

What are you using on the client PC to try to connect to the server? Are you trying to use SSMS? If so, the same goes for it. Make sure your version of SSMS is compatible with the version of SQL Server you're trying to connect to.

pallem · Jan 3, 2020

Hi, I have our access front end on the client PC which I am trying to connect to the Sql Server Backend database via an ODBC connection which works fine on our current network. I am trying to setup a new server using all the same software and ODBC drivers but I must have missed something in the setup. I had this problem a while back but cant for the life of me remember what the solution was. I know its normally something in the server firewall rules or the SQL server setup. I've disabled the firewall to rule that out at the moment. I'm looking for a set of step by step instructions if they exist that I can use as a check list and keep for future reference.

theDBguy · Jan 3, 2020

pallem said:
Hi, I have our access front end on the client PC which I am trying to connect to the Sql Server Backend database via an ODBC connection which works fine on our current network. I am trying to setup a new server using all the same software and ODBC drivers but I must have missed something in the setup. I had this problem a while back but cant for the life of me remember what the solution was. I know its normally something in the server firewall rules or the SQL server setup. I've disabled the firewall to rule that out at the moment. I'm looking for a set of step by step instructions if they exist that I can use as a check list and keep for future reference.

Hi. You may have already seen these, but posting them anyway in case it helps you or others in the future.

SQL Server Installation Guid

Install and Setup SQL

MS SQL Server Installation

Configuring Microsoft SQL Server

theDBguy · Jan 3, 2020

Some more links:

SQL Server Setup Checklist eBook

SQL Server Installation Checklist

SQL Server Central Installation Checklist

Hope that helps...

pallem · Jan 3, 2020

That's great, many thanks for the links. I shall have a read through.

theDBguy · Jan 3, 2020

pallem said:
That's great, many thanks for the links. I shall have a read through.

You're welcome. Please let us know how it goes. Good luck!

ahmed_optom · Mar 2, 2020

i had to download and use the sql odbc version 17 driver to work with sql express 2019.

can you connect to the server using ssms?

when you installed sql, did you do it as an instance or default? if default, then there is no instance needed.

Problem creating ODBC connection to SQL SERVER EXPRESS (1 Viewer)

Plem

Registered User.

theDBguy

I’m here to help

Plem

Registered User.

theDBguy

I’m here to help

Plem

Registered User.

theDBguy

I’m here to help

Plem

Registered User.

theDBguy

I’m here to help

Plem

Registered User.

theDBguy

I’m here to help

pallem

Registered User.

theDBguy

I’m here to help

theDBguy

I’m here to help

pallem

Registered User.

theDBguy

I’m here to help

ahmed_optom

Registered User.

Similar threads

Users who are viewing this thread