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":

Click here for the Work Files of this series:

[vineethkumarankam]

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

[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?

[ddoice]

Thanks a lot!

In my case the backslash + sql server name was the missing piece of the puzzle

[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

[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 ;-)

[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

[olubolaolunuga]

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

[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

[tomservo]

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.

[aliahmed]

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.

[kuranam]

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

[MarcusMackaku]

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

[AmbaPrasadReddy]

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

[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

[erichansen]

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.

[chjayakrishnajk]

will it work outside network please help me

[Awalreal]

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?

[wooripharmacy]

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!

[dungnguyen-swmm]

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

[srdjanpanic]

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?