10. (Advanced Programming In Access 2013) Using DAO to Connect to SQL Server in VBA
ฝัง
- เผยแพร่เมื่อ 15 ก.ย. 2024
- Using DAO to Connect to SQL Server in VBA 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...
I'm really sure your lesson is best!!! I keep playing your lessons on Access programming... for very long time...
Thanks very much!!! I really appreciate.. always..
Thanks again for such a high quality tutorial. I really enjoy to watch and listen to your videos. You have a very friendly voice and very clear to understand for people with another native language :).
Thanks again, kind regards, André (The Netherlands)
Thank you for showing what references you are using. I see so many videos that don't and it can be discouraging to try and find out why code isn't working.
I found that I can only get the connection to the database when I am more explicit:
Set db = OpenDatabase("Northwind", dbDriverNoPrompt, False, "ODBC;DATABASE=Northwind;DSN=Northwind")
The DSN connection was created in the previous ODBC tutorial and not this so that may be a factor.
Hi, could you elaborate further? I'm having the same problem, and resolved it with your command
Thak you this was my problem also
In Access 2019, OpenDatabase is asking for manual selection of DSN even when explicitly coded, that is:
Set db = OpenDatabase("Northwind", False, False, "ODBC;DATABASE=Northwind;DSN=Northwind")
Help.
Why it is DSN-less connection when in the connection string we put the DSN name?
While inserting data from sybase to Microsoft Access database throwing an error syntax error insert into statement please help me.....
Hi, Steve. Doesn't dao require to close the connection and the recordset?
Steve, quick question, I was able to link ACCESS to SQL EXPRESS thru management studio 2012, After a couple of issues with some fields, I was able to fix everything and it works fine. So the question is, can I deploy this SQL database to an AZURE account so I can have multiple users connected thru the web to AZURE? Have you ever tried? any reference I can look into?
thanks..
Yours videos are amazing!
Great content.
Hi Steve ! Thanks so much for your help .... can you show us how To alter a field To sqlserver Table from DAO, because there is no single documentation on this issue , and can not add column to linked table too ! Thanks
Hi again Steve. I have played around with the User DNS connection and manged to make the OpenDatabase action work by using a zero length string in place of the DSN name of Northwind. A list of Machine Data Source names including Northwind shows up and when I select Northwind the code runs listing all the Company names. The code line I used is as follows:
"Set db = OpenDatabase ("", , , "ODBC;")
When I substitute the Northwind name in place of "", I get the error of not being able to find the file. Thanks again for the great series and I will continue to work with and get it to work
+Mark Buckland Odd, if you named the DSN Northwind then it should be found.
Thanks for the wonderful tutorial!
I am wondering if this option can prevent Hex Editor from reveal my front end file?
Would it be the best option in term of security?
Thanks you!
Hi Steve, if I have more than one building, is that a factor? do effect the speed if I deployed database as access FE and SQL server BE?, Thank you!
Bilal Kareem Yes, any FE/BE arrangement will be affected by network speed. However, this can be managed by making the data you request smaller. Make sure all of your SELECT statements are narrowed down to just those columns you actually need, and you use where clauses to get just the rows you need.
Programming Thank you Steve.
Thank you so mmuch sir, this actually has help me as a beginner. please is it posible for me to email you on some question on challenges am having current so you could walk me through them?
why not do a debug.print of the customers.connect to get the conn string
Nice tutorial! How do you calculates in Excels students first term, seconds term an third terms in "sheet 3"?
Hi Steve this is a great video that i've been searching for a long time. Question? so having to define the sql connection string do i still need the link tables to store new record and changes ? 2nd, how many connection strings can i establish in one access db?
i made a connection but not don't have idea, how to import a table from sql to access
DAO can be used for any database server connection? eg mysql
I was not able connect to the Customer table until I updated the OpenDatabase parameters to this: "Northwind", dbDriverNoPrompt, True, "ODBC;DATABASE=Northwind;DSN=Northwind"
Dang it!!! Outstanding video, but nothing in your playlist on Stored Procedures in DAO? :(
Steve, thank you for sharing this video. I am actually having issues with the ODBC connect when I tried to get results/recordset from a saved parameter query (called in MS Access) or stored procedures (called in SQL Server), I got an error "item not found". Does DSN-Less connection not allow to perform Dao.Querydef? I read about that I have to do ODBCdirect connect. Can you provide help with this? Thank you in advance.
TM
ODBC Direct Workspaces were a powerful way to connect DAO to SQL databases in a similar way as with ADO. Unfortunately Microsoft removed that feature with Access 2007.
Hi Steve! Thanks for this Awesome Video.
I just want to know the best way to check that a connection is successful or not.
That is, getting back a true Or false rather than getting the SQL Server Error.
I am planing of putting it in my splash form on load event..such that when the end users launches the front end it checks for connection to the server if successful then opens the login form and if not successful then displays a user friendly message. Thank you!
Awal Saani Well you can do a "on error resume next" just before you run your opendatabase function. Then do a count on how many tables are found in the database. If tabledefs.count > 0 then you're connected.
Programming Thanks!
great as always...
Hi Steve,
Thank You for the Help.
Can you please help me with my Issue.
I am having some Issue with the logic DAO.
Actually I have a legacy application and was developed in access 97, Access 2002.
And now we are in access 2013 (we cannot go back older versions)
And I am not able to debug as the Initial load itself is failing with the error
Compile Error: Can't Find Project or Library
When I go to Tools ->References it says the that Microsoft DAO 2.5/3.5 Compatible libraries are missing
And since I am using access 2013 I cannot get those libraries.
Can you please give some suggestions to fix the Issue?
Have you tried referencing the newer version of DAO? If that does't work then you will need to fix all of the places in code that are trying to use the old DAO library with code to use the new one.
Hi Steve,
Can you please help me doing it.
How can i find the new version of DAO.i.e
what is the new version of DAO.
or Can you please suggest any sample for replacing the code
Thank you Steve.
VERY GOOD
Thank you.
Thanks Steave.
Hi Steve, still enjoying the videos. Now up to No 10 in the advanced course; however, I am having a problem with creating the User DSN to enable me to open the Northwind Db using VBA. I get to give the connection the name of Northwind , but Server name gives me 4 options as follows:
(local)
(local)
Main-PC
Main-PC
When I select either it comes up with the following errors
SQLState 0100
SQLServer Error 2
SQLState 0800
SQLServer Error 17
SQL Server does not exist or access denied.
I would appreciate if you could assist as to where the error is
Regards
Mark
+Mark Buckland try using the name of your computer and the instance name of the sql server. You can find the name of the sql server by opening up your services panel.
Then enter it in like this: ComputerName\MSSQLSERVER
+Programming Hi Steve, thanks for the assistance. I have successfully created a User DNS connection and it tested OK but when I try run the code in the test module an error occurs when I try to open the Northwind db. It says "Could not find file Northwind". Can you assist again. Thanks in advance
Regards
Mark
I have no way of knowing your settings. I'm afraid you need more direct assistance.
+Programming Thanks for the reply. I will endeavour to find the source of the problem. Thanks again for the series of videos, I have learnt a lot.
Regards
Mark
I think the lecture series is great - big thanks, its definitely filing in knowledge gaps for me (approaching Access from a SQL Server experience) - However you made one comment about ODBC vs OLEDB which I think is incorrect. OLE DB is newer than ODBC and in fact utilises ODBC drivers. OLE DB is, also richer in functionality - in that it can also connect to non database files - but is proprietary to Microsoft and so may not have the cross platform reach of ODBC (obviously connecting MS Access to MS SQL is not an issue, and may be better in a Microsoft use scenario). Lastly some good news about OLEDB: community.spiceworks.com/topic/2090199-microsoft-announces-that-ole-db-is-no-longer-deprecated
Very interesting! Unfortunately, I can find any way to do a real query instead of opening just the whole table. That makes rather unusable in real projects.
+codekabinett.com/en Keep watching. Each video builds on the previous ones.
Hi, excelent course, i work with Oracle almost back end process, to create another layer of security i use Views and instead of.
Ej:
CREATE OR REPLACE TRIGGER TG_VWUNIDSINS
INSTEAD OF INSERT ON VWUNIDS
DECLARE
BEGIN
if :new.TIPO = 'E' then
pkPLANESA.EmpresaINS(:new.NOMBRE ,:new.DESCRIPCION);
elsif :new.TIPO = 'U' then
pkPLANESA.UnidadINS(:new.NOMBRE ,:new.DESCRIPCION ,:new.PAPA);
elsif :new.TIPO = 'P' then
pkPLANESA.PuntoINS(:new.NOMBRE ,:new.DESCRIPCION ,:new.PAPA);
end if;
END TG_VWUNIDSINS;
/
I think this is a good technique, again tks a lot for the tutorial.
Triggers and views are a pretty good practice for locking down access and managing the data flow. I highly recommend doing it if you have the time and resources.