6. (Advanced Programming In Access 2013) Creating An ODBC Connection To SQL Server

Creating An ODBC Connection To SQL Server video in the "Advanced Programming in Microsoft Access 2013" series hosted by Steve Bishop. In this free advanced video tutorial series Steve will be going over Microsoft SQL Server installation, Database Migration, creating a better User Interface, using external data sources, complex Visual Basic For Applications (VBA) concepts and distributing your application.
Click here for the full playlist of "Advanced Programming in Access 2013":
• 1. (Advanced Programmi...
Click here for the Work Files of this series:
github.com/Xip...

[@earbilla]

Brilliant explanation. I have been doing work with Access over SQL for a long time and I wish someone had taken the time and had the ability to produce a helpful, clear and concise guide as this is. Fantastic work. All the best.Regards. Ernesto ArbillaArbilla Pty Ltd

[@ProgrammingMadeEZ]

+Ernesto Arbilla Thank you Ernesto! Really appreciate the kind words.

[@djaronso]

Absolutely brilliant explanation. I've been trying to sort out my connection for 2 hours until I found this video. I was using piped connections before and they didn't work. Microsoft support wasn't good either. You absolutely nailed it! Thanks very much for this video

[@ProgrammingMadeEZ]

You're welcome!

[@wooripharmacy8561]

I just spoke to Microsoft professional services employees. The question that is answered here is worth $500.00 from the Microsoft support team. Thank you very much for posting this video. Subscribed and liked!

[@olubolaolunuga3367]

!!! Absolutely Brilliant,,, you are Saviours of our time.., for me the video is the best of all videos about Data Source ODBC configuration,,,,, it is the biggest of all.

[@wsitech]

Although it's an old video I'm glad I came across this. It's easy to set up an ODBC connection in Control Panel, but as you said that can't be "copied" to the different user PCs which use that database. We use a customized VBA procedure that "manually" re-connects the tables, but knowing how to do this in group policy so that the ODBC will work on the other computers too will save a lot of time. I assume though, that this also depends on the network setup and policy which as a consultant, can differ greatly client to client. Also what's the advantage you see in using SQL Server Native Client vs. ODBC version 17?

[@td4269]

So clear! Very well explained. Thanks

[@Wondrous_Wisdomball]

The most helpful and just a magnificent video of all time! Thank you, Steve, really , thank you!

[@AmbaPrasadReddy]

Hey Steve, These videos are very helpful buddy. thank you very much for the videos.

[@elias-9395]

Excellent work. Very thanks

[@vineethkumarankam2343]

Could you please create a video on " how to publish ms access to online database"..it is very much needed

[@MarcusMackaku]

To see which bit system you are using, go to Control Panel -> System and Security -> System.

[@MarcusMackaku]

It is labeled 'System type:'

[@Goldensky-zs2lm]

The note of odbcad32.exe was very helpful. It solved my problem.

[@aliahmed16314]

Hello Steve, thank you very much for this series its very helpful, just asking is there another way than ODBC connection? some thing like OLEDB or ADO.Net, or some thing open connection by vba and close it when finish, because the ODBC make heavy network traffic, and thank you again.

[@ddoice]

Thanks a lot! In my case the backslash + sql server name was the missing piece of the puzzle

[@morphos2]

I was hoping you’d taught us how to do this programmatically with VBA. I really needed that. :(

[@SiyamandRashid]

thanks for all great videos...im confused about one thing here obd data source 32 is related to windows version or office version lets say i have windows 64bit and office 32bit should i use odb 32 or 64? many thnks

[@Awal4real100]

Awesome! Thanks!

[@ProgrammingMadeEZ]

Thank you, I'm so glad you liked it!

[@tomservo75]

Can I ask, because I often encounter slightly different screen sequences when connecting SQL tables in Access. Sometimes it asks for an ODBC connection, sometimes that window doesn't pop up. Also I notice there's an ODBC SQL Server version 17 and a "SQL Server Native Client 11.0". What is the difference among all of these? OLEDB, ODBC, Jet, I get all of these mixed up.

[@srdjanpanic8109]

First of all thank you Steve for all of your tutorials which are more than helpful. If SQL server tables are linked in Access, will data changes done in access (update, append, delete query etc.) be reflected in SQL tables? Or is that connection only for reading data from SQL server?

[@kuranam]

How to make an executable file of Access that use another Database engine like SqLite, Postgresql, etc?

[@dungnguyen-sw4mm]

Thank you so much. Do Odbc not support fully Unicode? Query did not working with Unicode text (Vietnamese)

[@SirTKC]

Great video. It would have been a very nice to have if you would have covered the SA setup authentication vs Windows Auth. With an Access front end, that annoying SA Login Pop-up is a killer for every single users. Is there a way to avoid (or going around) this ? Maybe in your next great video ;-)

[@sahalasolusiindustri]

awesome tutorial, I have project insert data C# to MS ACCESS 2016 doesn't success yet. Would you help me for Connection String MS Access 2016? thanks

[@chjayakrishnajk]

will it work outside network please help me

[@farmanalishah8836]

Hi dear, Thanks for the amazing tutorial, How to configure the SQL for multiple users on different PC,s?

[@andersarvidsson2677]

Can we do this in the opposite direction? Is it possible to link tables in a .accdb file to a SQL Server Database?

[@danielrivera3702]

Sorry ..Steve.. I am covertion a Database of access to SQL. I have images store in my access database as 'attachment". I need to export to my SQL . What type of field should be in my sql?

[@rhildebrand1128]

How do I do the group policy part? I can access the table but my co workers cannot

[@102RC]

clearly explained...

[@SuaveBajaj]

In the ODBC Data Source Administrator (64-bit) the platform shown in my PC is still 32-bit.... HELP!!!!!

[@25ciskey25]

now how do you connect to this data source using C#?

[@Awal4real100]

I have learn a lot from the series. Can't wait for the rest of the series. I use the up-sizing wizard in ms Access 2010 to upside my tables and data to SQL Server 2014. Everything works fine except I can't add new records to my sub forms. I can only retrieve the records on the sub forms. Any help with this issue?

[@ProgrammingMadeEZ]

Awal4real100 Make sure you are including the primary key field and have given it a textbox on your form. You cannot make new records on a form without the primary key field being bound to a control.

[@Awal4real100]

It works! Thanks.

[@arzarmm4486]

i dont have the sql server...how can i download it?

[@haydarm.al-samawe9819]

Hi steve i know this corse from long time ago .. but i had work with the same idea .. everything work normally with me but the problem in edit record .. sql don't response give me message as another user use this record or something like that .. the database created by adp.net and i need to link it to access .. any idea you can help please

[@adityait11]

Very good Explanation.But I am working in replacing the access data base to sql data base. But keeping the Access front end same. Finally I need to distribute this to my end user around 20 people(who will be doing all the Inserts and Updates to data). so can you please suggest what how to handle the situation. Like what Installation to be done at the end users machine. And do I need to get them access to sql server. Please advise.

[@akkavin]

Hello Aditya... Hope your project was successful... It would be a great help if you can contact me through [e-mail] as i am currently involved in the same project as you have described.. I have few questions that i need clarity on... Waiting for your reply..

[@dumitru322]

Hi, what should I write into Server box, if I want to share my Front End to other clients? I tried to write IP\instance; IP,port; IP\(local system)\instance, and did not worked. What is my problem? P.S. I had enabled SQL Server, Browser and TCP/IP services. Thanks for your help,

[@ProgrammingMadeEZ]

It depends on where your server is and what instance of SQL Server you are running.

[@leandrolima9078]

Hi, thank you for making the best access 2013 advanced course in KZbin :) I'm facing a problem to create an ODBC connection to a remote server. Could you please clarify what are the settings and steps to make it? I really need it to implement a solution. Thanks again!

[@ProgrammingMadeEZ]

+Leandro Lima What are you missing from this and the other videos that I made which explain this?

[@leandrolima9078]

How to create the ODBC connection for a server that is in your network but not in your computer. I think it's not possible using SQL Express, but I'm not sure =/

[@ProgrammingMadeEZ]

I explain that in this series. Just replace the localhost name with the computer name of the server that is hosting the SQL Server.

[@andersarvidsson6560]

Well, I have done Everything you said, but it still doesnt work:The server is running on the remote computer, the instance name is correct, the server is configured to allow remote Connections.Microsoft SQL Server Native Client Version 11.00.2100Running connectivity tests...Attempting connection [Microsoft][SQL Server Native Client 11.0]SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF]. [Microsoft][SQL Server Native Client 11.0]Login timeout expired [Microsoft][SQL Server Native Client 11.0]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.TESTS FAILED!

[@ProgrammingMadeEZ]

+Anders Arvidsson you may not have TCP/IP enabled on your SQL Server.

[@adityait11]

And also can you please advise which version of ODBC drivers I need to attach my DSN As I have 64 bit But not sure how my 20 end users are using.

[@ProgrammingMadeEZ]

It is dependent on the version of Access you're using. If you are using Access 32 bit then use the 32 bit driver.

[@danielrivera3702]

Hi Steve.. On Access i have the attachment. Where i store image. What is the convertion on MSQ?

[@ProgrammingMadeEZ]

I'm sorry but I don't understand the question.

[@dominicdukes3566]

HAY I FOLLOWED UR VIDEOS PERFECTLY AND SQL IS RUNING ON MY SERVER , MY DATA BASER WORK ON THAT MACHINE BUT NO OTHER MACHINE CAN CNENCT TO IT IN THE OFFICE ?

[@dominicdukes3566]

WOW SORRY FOR POOR TYPING ITS COLD HERE MY FINGERS HURT ;)

[@lifestyleworld6821]

How to establish ODBC connection to test instance?

[@ProgrammingMadeEZ]

The same way you create one to the production.

[@harisrg92]

Hi, I was just wondering where does the data filtering actually happens. For e.g In my access project, I have queries that make use of functions which are defined inside the modules of the front end MS-access file. I use those functions to filter out data in the way I want. So does that mean data is filtered on the front end ? Also when I use predefined sql functions like AVG or COUNT or MAX, is it the sql server that already filters the data or is it my front end that gets the whole table and than filters the data?

[@ProgrammingMadeEZ]

It depends on if you're doing passthrough queries or not. Passthroughs send the query to the database engine where as standard queries will use the ACE engine that comes with Access.

[@harisrg92]

The query in which we do query design in MS-Access and use AVG or COUNT or MAX and keep that query on the front end is not a pass-through query, right? Also does it really matter in terms of speed? Should I go for pass-through query?

[@ProgrammingMadeEZ]

Yeah it sounds like you have them client side. Pass through queries will always be faster because the ACE engine doesn't have to do any processing of the data.

[@harisrg92]

Thank you so much :)

[@isukhova]

i love you

[@alanparker6723]

After installing the SQL on my computer, adding an ODBC connection to other computers on my home network doesn't work for me. Even though the other computers see my computer, they don't see the instance of SQL. What am I missing? Do I need to use XAMPP?

[@ProgrammingMadeEZ]

You may have to put in the pc name AND the SQL Server instance name. So, for example, STEVES-PC\SQLEXPRESS. It may not detect the SQL server if you just search for the SQL Server by computer name.

[@alanparker6723]

I did that already, but the connection fails. Do I need to install SQL Server in all the computers? Also do all the computers need to be running the same version of Windows? I have the database in Windows 10, but the other computers are running Windows 7. I already turned on the TCP/IP protocols. I just finished watching a video on how to access the SQL remotely, and both computers have to be running SQL Server, but I don't think that applies to a home network.

[@ProgrammingMadeEZ]

You don't need SQL server on both computers, just the one hosting the data. I don't know exactly why you cannot connect. My best guess is that your firewall is preventing it.

[@alanparker6723]

I read that the client computers must have a least the drivers to detect a sql instance on the network, but I don't know where to get them if not from the sql server software itself, unless they come down on a Windows update.

[@ProgrammingMadeEZ]

SQL Server drivers are installed with Windows by default. But you can download the latest one here www.microsoft.com/en-us/download/details.aspx?id=36434

[@myksacdalan]

Hi Steve, I've been following your tutorials, I tried connecting to the SQL server using another computer within the same network. The server has a 64-bit OS, 64-bit SQL Server 2014 & 32-bit Microsoft Office. The other computer has 32-bit OS and 32-bit MS Office. When I tried to create an ODBC Data Source, the server is not available in the SQL Server choices. I just typed it in the Server, but then in the default database, I got this error: Connection failed: SQLState: '08001' SQL Server Error: - 1 [Microsft][SQL Server Native Client 11.0]SQL Server Network Interfaces: Error Locating Server/Instance Specifies [xFFFFFFFF]. Connetion failed: SQL State: 'HYT00' SQL Server Error: 0 [Microsoft][SQL Server Native Client 11.0]Login timeout expired So, I cannot use my Access front end with other computers. I appreciate if you could help me on this. Thanks!

[@ProgrammingMadeEZ]

You need to create an odbc connection with odbcad32.exe in your windows/syswow64 folder.

[@myksacdalan]

Programming yes, i did that to the server and it was successful.. But when i tried it with other pc, i got that error. What else can i do? Thanks.

[@ProgrammingMadeEZ]

Michael Sacdalan If it's timing out then it's probably something to do with your network. Double check the address and that the firewall isn't blocking your connection.

[@erichansen3641]

I have Windows 7 Home Premium. Microsoft Jet Engine comes factory installed. I am able to use the ODBC Administrator to create empty Jet 4.x format *.MDB files that have unicode support, compression, and encyrption turned on. Then I use the Win32 Perl programming language (ActiveState, ActivePerl, 5.6.1 binary build 638) with its Win32::ODBC module to run SQL statements that create the Tables and Indexes (or Contraints) within my empty *.MDB files. My Jet 4x relational database is around 1 Terabyte and 5 billion rows. I maintain the optimization by using the Jetcomp40.exe utility that can be ran in batch mode. Takes about 5 minutes to: optimize, repair, reorganize, and compact, a single MDB file containing 10 million rows of data, including a MEMO field, which I had loaded (from external flat files) via Win32::ODBC from within Win32 Perl. 5 Minutes on a modest Laptop with 1 x 2.2 GHz processor, and 3 GIG RAM. No telling how fast on a robust Windows Sever with plenty of RAM? Jetcomp40.exe is a memory hog. It uses very little CPU resources. Because of this, I switched from running Jetcomp40.exe as many concurrent/simultaneous "detached" Windows background processes, to running Jetcomp40.exe sequentially (wait for one process to complete, then start the next, until all MDB files were processed). For reporting from this huge Read/Write Database, or Read Only Data Warehouse, I use Win32 Perl and its Win32:OLE module (COM Automation) to start and connect to MS-Excel in the background, to create formatted reports from data retrieved from the Database/Data Warehouse via Win32:ODBC. When I write to the Database, I use Transaction Processing, using Commit and Rollback to write "all or none" of the changes to the database should an error (such as a dropped network connection) occur. I have used this techology since 1998 and don't plan on using anything else to replace it. For DB user-interfaces, I use the native Windows GUI Win32::GUI module to create event-driven applications. Win32 Perl with its: Win32::ODBC, Win32::OLE, and Win32::GUI modules have been a mainstay for me since 1998. Since 1998, I have also used the IndigoSTAR "PL to EXE" compiler to create freely distributable, standalone, Perl application programs. I may be the only person on the face of the earth doing this? Unfortunately, in the IT world, it appears to matter more if a person has particular software skills more so than can they get a particular job/task done accurately, cheaply, and fast. What I have explained here is stable, 100% FREE, database technology - requiring no 3rd Party Software costs nor corporate or end-user licensing costs. And a developer can easily maintain such as database system themselves without the cost of hiring a highly skilled DB Server database administrator. Maybe I should move to a 3rd world country where my talents might be appreciated? Sorry for being preachy, but this is a pet peeve of mine.

[@ugh3012]

No subtitles for the deaf. Zero inclusion.

[@ProgrammingMadeEZ]

How about asking nicely instead of being a jerk about it?

[@ugh3012]

Programming Made EZ Why do we have to ask in first place? If you were in my shoe, you would understand my frustration.

[@jamiemarshall8284]

This is an awful solution. You'll have to set this up an any computer who wants to use this access file.

[@gabrielaestefania7814]

Awesome! thank you!!