7. (Advanced Programming In Access 2013) Linking Tables From An ODBC Connection To Access

Linking Tables From An ODBC Connection To Access
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...

[@rickadase662]

Great lessons. I looked all over the internet and found several that explained all the steps to link Access to SQL Server except the one in changing the server instance from "master" to the database I needed. No one ever mentioned changing the dropdown. I looked over that choice many times. Then I found you and now I'm happy!!!

[@wilsonsantiago39]

wow, I am refreshing my access and had you on my links stop by to take a better look ... I noticed you have links to the tutorials ... that is awesome .. Thank you

[@ProgrammingMadeEZ]

Yes. Work files are often very helpful.

[@bolarazaki1864]

Hi Steve, When using a SQL Server Database with an ACCESS interface, is it better to link the table and use them like a regular access database (which means using the ACCESS engine)? Or is is better to create an ADO/DAO connection and run all the queries on the server itself? When i say better I mean performance wise.

[@altonbrisbanejr8254]

Great info as usual. I have read that some feel that it is better, more secure, to not link tables but use a pass-thru-query or some other method where users don't have access to the tables. I understand that they can't change the link or at least modify the table thru a link. Just looking to see what is more secure. I know that table/object permissions can be assigned on the SQL Server side but wanted to get you take on this. I have even read that some think that connecting to the SQL Server is better using an .ini file based on deployment concerns. Again thanks for taking the time to do these tutorials.

[@ProgrammingMadeEZ]

+Alton Brisbane Jr I cover this later on in this series as a matter of fact.

[@amahroos]

Thanks

[@markwilliams9771]

I set the primary key for the Suppliers table in step 5, but I also changed the Identity Specification to Yes because the data type is Autonumber.

[@tjanakiram]

Greetings Steve, thank you for this great information. i have some requirement in project. after connecting sql tables to access database. while in same time i want load required columns( which i want) into excel file. ex:customer table has been linked .from this linked table.i want pick only specific columns load into excel file in my system(any location).can you help me

[@avinashjakka4317]

Hello I want some help from your side I want to view the relationship/backend quries/foreign keys/ etc of existing database in MS Access please help me on that

[@jooryalhamed938]

Hi, please make videos on how to share sql database with multiple computers

[@kevinmcloughlin5988]

I am deaf and I really wish this video had subtitles. I have tables in MS SQL Server that need to be added into MS Access and be able to have MS SQL Server automatically updated whenever I make changes in MS Access

[@josephchacko869]

What should one do if I have two tables with same name and schema but are in different databases?

[@bobbanovski1]

Hi Steve, thanks for this video series - very good explanations Is it possible to use this method to keep the data on the Access database, whilst updating the same tables on the sql server when the access data changes (sending data in the opposite direction)?

[@mks2613]

great video..thanks

[@dumitru322]

Hi Steve, I have migrated my back end database to SQL server, after that, I linked the tables from ODBC connection to BE. But, my front end don't see the tables from BE. What can I do ? Thank you for your help.

[@ProgrammingMadeEZ]

+dumitru purcel You don't link the connections to your back end, you link them to your front end. You no longer need a BE file now that SQL Server is hosting your data.

[@dumitru322]

Programming ohh, thank you so much, Steve. Great tutorials!

[@mafgeology]

Many thanks for your videos After connecting Access with tables on MySQL workbench using ODBC and my test was for connection speed and performance was as following Table locally in Access DB and same on MySQL (Consist of 3 columns; Auto number and two rext colums) Test was Open DB and open rs Loop to insert X numer of records using .Add new .Field(1) = "sample" .Field(2)="Sample" .Update While inserting 100,000 records for example the local test was only 1 second but the append process to the connected table via odbc was slower 70 seconds The ODBC Was with local db not via network. Please advise is this speed normal and if normal it got stuck while querying 25,000,000 records or inserting 1Million records via ODBC Also it takes long time around 10mins to export 25Milion records table from access via ODBC to MySQL workbench. Many thanks in advance!

[@abdulsalamasadeq3298]

Greetings Steve, thank you for this great information. I am able to connect my tables from an ODBC connection to Access. I can add , update and delete records from both side with no issues. Also, I am bale to able to connect my hosting sql server with no issue.The issue that I am facing is with the queries showing very slow performance. Do you suggest any ideas to improve the performance.Regards,Abdulsalam

[@ayubkarem8697]

Hi Steve,thanks for your great videos , i could implement all steps , but the performance of retrieve data from SQL server is too slow ! what should i do in this case ? thanks for response warm regards

[@TOM-vk7du]

Hi Steve, Thanks for your great tutorial videos. I wanted to know if Ms Access (Desktop) can be shared on the internet. I know of Microsoft WebApps which cant take VBA coding currently. I would very much appreciate your response. Thanks

[@chiranjeevibeeraka991]

Hi Steve, thank you for the video. I have a challenge that when I am opening linked tables , getting ODBC connection failed. Could you suggest how to fix this issue

[@RuffinRhodes04]

Is there a way to copy the ODBC setup from workstation to workstation easily without the setup routine ( I have over 25 workstations)?

[@ProgrammingMadeEZ]

I recommend you use Group Policy to do this if you are a part of a domain. If not, there might be a power-shell script which you can run. If neither of those two things are an option, then you are either left with doing it one at a time, using Pass-Through queries only with a stored connection string, or going DSN-less. DSN-less is a concept I will be going over later in this series, but essentially it means not using the "Record Source" property for any of your forms or reports. Instead you use VBA to connect to the SQL server directly using a connection string. You then make a recordset from that connection and use the recordset to output your data to the screen.

[@RuffinRhodes04]

Thank you very much. We have a domain so I will look at setting up a Group Policy. Again...I appreciate your tutorials. Very helpful!

[@emmanueluko4817]

Am using sql developer to do my migration from access but the datatype is the issue. sql developer does not have the same datatype as access. What do i do at this point to have the same datatype like access?

[@abdevillier679]

How to share the connected Ms access form to network..so that other users can also use the same front end form

[@myameen7062]

can we use validate rule on linked tables in access ??

[@maherkarim693]

Hi Steve. Cannot delete a table since it has relationships to other tables. How do I account for those relationships in SQL?

[@boomwithpeter623]

Does that link option means that it keeps the copy of all tables localy and makes exact same copy on the MSSQL Server or it means that it reads and writes directly from the server. Thanks in advance

[@habibjobs1878]

i like your video i need to developed our access file to software and also link other software to access

[@danielzapata9857]

I have issue when I split my access database. I working using VBA and when I don't split split my database I don't have Issue with my combo box. If I tray to search information from one employee for example I can display information on my text box but nothing show on my combo box. If you need the code let me know I can send to you.

[@ProgrammingMadeEZ]

Daniel Zapata Make sure you rename the table to the correct table name. When you link a table back to Access it will put a dbo_ in front of it.

[@ManojYadav-nt2rm]

I am getting the error 2037 when converting from 32 to 64 bit

[@Rob-fj9nr]

Hi Steve, great videos.. thank you. Is there a way to link the tables automatically from VBA without having a DSN connection defined on the machine?

[@ProgrammingMadeEZ]

You can use VBA to set the connection string of the table, though typically I prefer going without linked tables except to help look things up. As you improve your skills you'll find using DAO and ADO recordsets to fill in your forms and reports is much more robust and faster than relying on linked tables.

[@ngnews24-tv13]

Hi Steve,thanks for your Videos, they are very educational. I am presently creating a front end for my sql server and I will like to do all these table updating and liking from an access form(GUI). I will have to give the application to a customer at the end and I don't want them to be doing this updating link manager manually. I want a situation where the tables are automatically updated as soon as the form is opened or a user is logged in. Is there any way to go about this. Is Macro an option and if yes, how do I go about it. Thanks coach

[@ProgrammingMadeEZ]

S.I. CIMTHOG Yes VBA is in order and it's a scenario I plan on going over very soon in this advanced series. Essentially you need to go through all of your tabledefs and set their connection string to the SQL server.

[@lindabuckland6515]

Hi Steve, thanks for the great series of videos. Up to No 30 Advaced and learning a lot. 1 question, I note that some of the tables in Northwind are not updateable eg Employees, can you advise on how to make them updateable. Thanks

[@ProgrammingMadeEZ]

+Linda Buckland I'm afraid this is not nearly enough information to diagnose the problem.

[@lindabuckland6515]

+Programming Thanks Steve, I have sorted the problem. I had a few tables without a primary key thus they only opened as read only. Thanks again for the great series of videos.

[@ProgrammingMadeEZ]

Ahh, glad you got it sorted out.

[@mohammadgilani9900]

Hi Steve I did the procedure from previous lesson but in this video I didn't get the database " NOrthwind .sys" on ODBC select Data Source on Machine Data Source Tab. Please advise Thanks.

[@ProgrammingMadeEZ]

Why would it be a sys?

[@Nabeeha1]

Hi Steve, once you linked ODBC tables into Access, if I create query from those tables to retrieve certain data field, how can I make a new table that automatically linked & refreshes data as in those ODBC tables? I am asking as I want to connect the table with that selected data field in Access from Excel. I hope my question makes sense... :)

[@ProgrammingMadeEZ]

+TimeIsMirracle You would need to use VBA, but I would recommend using a DAO or ADO connection instead of doing linked tables. You will just have more flexibility.

[@greencoder7419]

My access is working fine without removing the dbo. Do we really need to do that?

[@syriacas]

It does make a difference if you and others use the same tables and they remove the dbo. and you don't. You won't be able to share queries because the table names are now considered different even though they are the same. AND it's a real pain to always type or look for that "dbo."...

[@greencoder7419]

@@syriacas thanks

[@BorisBecerra]

Hello, thanks for te videos but I have a problem with my front-end. I did the migration to sql the the assistant for importation to sql, so I have my tables, key, and relations. Then I linked the front-end to the sql back-end, delete 'dbo_' . I can open forms but I can open some of them because The front-end doesn't have any relation. I don't know why, please.... tell my what to do. ¿Do I have to write any relation on my front-end again?

[@ProgrammingMadeEZ]

Boris Becerra You'll want to refresh the link through the link manager.

[@abiezercumaio1239]

[microsoft][sql server native client 11.0] query timeout expired (#0) I have this error when i try to do the link. Someone help. Tks

[@kevinzhao5632]

I have an access 2013 front end on the desk top of my computer, and a back-end in an other computer of the local area network with sql server 2014 installed. My question is how to connect the front end with the back end? Is it possible to connect the back end from another place out of the local area network via internet? Thanks, by the way, I am from China, is that possible to make a phone call with you?

[@ProgrammingMadeEZ]

+Kevin Zhao Yup, you want to use an ODBC connection as I demonstrated in this video.

[@coaching_engineer]

I'm assuming that you needed to put keys in all the appropriate tables in SQL Server, and you only prepare to show one as an example. Access ask for all of the tables from SQL server about having a primary key set.

[@ProgrammingMadeEZ]

Jaz Isom Yes. Sorry, that's a lot of boring work to show in a video so I did one as an example and left the rest to you.

[@coaching_engineer]

No worries. Just making sure that I'm doing this all right. Thanks!

[@alexisburgos8387]

Hello... I can do the linked tables to sql server, but, I have a question for you.... if I use my aplication in a pc on Lan and I conect to server, odbc connection always ask to me for the login in sql, I know that I can check the box on "Save password" but I want to do that from vba code, 'cause in that way, any user can stole data from the database 'cause automaticly access ask to him for user and password of sql.... do you have the code for do that from vba?

[@ProgrammingMadeEZ]

Alexis Burgos I'll do my best to answer your question but I'm not sure I fully understand the problem. ODBC connections can be set up to use either a saved SQL Account such as an 'sa' account, or you can use Integrated Security which is windows domain accounts. Windows domain accounts really only work if you are on a domain and all of the computers on your network are a part of that domain. A local SQL account is an account that the SQL Server manages and isn't related to your domain. In either case, the usage of credentials is passed on through the ODBC connection, not the VBA code or Access database. HOWEVER, there are things called DSN-less connections where you can open a connection directly to a SQL Server without using a stored ODBC connection. I will be doing a video on this later on in the advanced series.

[@magdalena5640]

how to make changes in Microsoft access if table is linked. I tried add one column and received message: "if you ant to add or remove fields you must do so in the source database" could you please help me on this

[@ProgrammingMadeEZ]

You need to use Sql Server Management Studio (or some other management tool) to connect to your back end database.

[@Awal4real100]

Hi Steve! I have problem adding record to my datasheet sub-form. It turns blank when I click on Add New Record. Is there any problem with my ODBCE connection to SQL Server?

[@ProgrammingMadeEZ]

This usually happens when you have a required field that doesn't have a default value set up in SQL Server, or you haven't initialized a value for every bit field. Did you watch the past 3 videos on the migration and preparation process?

[@saeed-eg8ze]

hi can i share access file to 20 computer without installing the form in each computer and link it to access table. plz help in this. what if i updated the form

[@ProgrammingMadeEZ]

+saeed I suggest you go back and revisit my video on splitting the database in the basic series.

[@icarefinancial840]

Hello Steve, How do you call the ODBC connection in vba when your front end opens? Do you have a tutorial on this?

[@icarefinancial840]

Please and Thank you

[@ProgrammingMadeEZ]

What do you mean call the odbc connection?

[@vl5888]

Hi Steve, How can i automatically update the linked table from the ODBC? Thanks in advance!

[@ProgrammingMadeEZ]

Vincent Liban Loop through the tabledefs and assign a connection string to the tables connection property then refresh the table.

[@paulramirez7792]

Once linked, is Access still subject to the 2GB limitation?

[@cooreypan1856]

I would also like to know if we have to reconfigure the ODBC connection on every PC that uses this application once it is done this way? I would like to have this distributed among quite a few PCs in the office and it would be a huge headache to have to establish this connection every time someone new needs the application especially if the Admin department needs to get involved for network permissions.

[@rathpanhasarun310]

I use remote connection in my front end file, so I have to change my connection from remote to local in vba code right?

[@ProgrammingMadeEZ]

I'm not quite sure what you're asking.

[@rathpanhasarun310]

That's okay! I solved it already.

[@completelystupid87]

What happened to all the relationships between tables that we made before?

[@ProgrammingMadeEZ]

Unfortunately they are not migrated with the tables. You will have to recreate them. I will be doing a video about this in the very near future.

[@RuffinRhodes04]

Programming Thank you very much for these videos!! They are very helpful. Just subscribed to your video.

[@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.

[@haidarathif8153]

is this tutorial worked in linked pc to server pc?

[@ProgrammingMadeEZ]

Yes.

[@johnwatkins39]

how to relink a database automatically, front end and backend.

[@ProgrammingMadeEZ]

Do a for loop or foreach loop on all tabledef's in the currentdb.tables collection. Set the connection property to the connection string.

[@user-tj5uh9io2f]

I have been following everything you have done until this point..... now I have an issue. I am using a VBA code that writes changes made to my data. The code works beautifully (as long as it updates a local table). I can't figure out how to refer to my linked table successfully. (ODBC error). So, I thought maybe I can take the local table and append it to a linked table (linked to my sharepoint azure SQL database). The information flows from my access web app to my local database just fine. I am creating forms and reports just fine, I can even update and add new records via a form, but I can't append to linked table, nor do I know how to call that table in a VBA code.Any help you can provide will be appreciated! Here's the code I'm using for auditing changes that users make locally.... .fontstuff.com/access/acctut21.htm

[@ProgrammingMadeEZ]

+nyaworld Did you already link the table? If so, make sure you rename it otherwise it will put the dbo_ in front of the name.

[@user-tj5uh9io2f]

+Programming Thank you for your response. I pieced together how to reference my table in SQL azure from a forum post. I kept changing the VBA code in the audit code until it finally updated the linked table! I guess I have to include the username, pw, etc so that it connects to that SQL table. I've created all my tables in Access Web 2013 APP and then linked them to a local database so I can run reports and customize the UI. I noticed that my linked tables have the word "Access_"added to the front of each table's name. So if I created a table called tblaudittrail in web app, and I link to it, the new name becomes "Access_tblaudittrail". Interesting. I'm going to continue to watch your videos as I have learned alot. I'm self taught and have managed to create a decent Access database so far. Thank you!

[@avmstephen9716]

are these videos legible. or my eye is blind???