How to Design Tables in SQL Express for Use with MS Access
ฝัง
- เผยแพร่เมื่อ 5 ก.ย. 2024
- In this episode, we take a look at how to craft a table in SQL Express (or SQL Server, or Azure SQL) for the purpose of integrating it with a Microsoft Access front end. The free SQL Express (formerly MSDE) has a long history of tight integration specifically with MS Access, and they were even shipped together for a long time. Building off of our last video in this playlist where we installed and configured SQL Express, in this episode we'll design our first table and demonstrate my top choices on how to build table fields in SQL Express that directly translate to specific field types in MS Access. This allows us to build and link tables that run flawlessly in the MS Access front-end environment, so that your form, report, and VBA designs execute exactly as you intend, but on a bigger, more scalable, more feature rich back-end database. Let's go!
Related Videos:
How I Reacted When I First Converted a MS Access App to Use a SQL Server Backend
• How I Reacted When I F...
How to Install and Use SQL Express
• How to Install and Use...
How to Design Tables in SQL Express for Use with MS Access
You are watching this video now!
Make sure to watch the previous episode on How to Install and Use SQL Express: • How to Install and Use...
Join me on Patreon!
/ mackenziedataengineering
Demo of my BZ RDP Cloaker:
www.patreon.co...
Check out some tech (and support my channel!):
www.amazon.ca/...
Want the code from this video?
mackenziemacken...
Interested in transforming your career or finding your next gig?
system.billzon...
Want my team to do a project for you? Let's get to it!
system.billzon...
Contact form you can find at www.mackenziema...
Follow me on social media:
/ mackenziedataanalytics
/ seamacke
/ seamacke
/ seamacke
/ psmackenzie
Get Microsoft Office including Access:
click.linksyne...
Got a TH-cam Channel? I use TubeBuddy, it is awesome. Give it a try:
www.tubebuddy....
#sqlserver #msaccess
Great video, thanks so much.
Glad it was helpful!
Thanks for a very good explanation of sql link table on remote sql server
You are welcome!
Yaaaaasssss! HEY, almost 10k!!!! Congrats!
Thanks I'm getting there! cheers
Thanks for your video
Most welcome!
Pls can you show us vb script to MS access to MySQL
Thank you, keen to see further as I just downloaded SQL Express and would like to replicate a current and complex Access db in SQL Express.....
Awesome - once you implement on SQL Express, you'll never want to go back. It really takes your app to the next level. If you transition well, there will be few changes to the front-end. It just runs like it did before.. .. but with a killer fast, robust, and scalable backend.
Respected Sir, Please make a video that how to deal with Multiple Checkbox field in Backend SQL Database for frontend of MS access.
Good idea for a video!
Hello Sean. I have certainly been enjoying watching some of your videos and getting some great knowledge from them. I recently tried your video on "How to Design Tables in SQL Express for Use with MS Access". Unfortunately, when I tried to enter data into the last three fields ie Currentsalary, SignedPolicies and madeUpScore, it would not let me enter any value into these fields!
I would appreciate it if you could help me with this. Please keep up the good work..Cheers
Hey Rusty, that's an interesting problem! Can you tell me the your driver name and version from the driver's tab in your ODBC administrator?
Hello,
I am interest with this top pic and tried, but I didn't know where is the best place to put on relationship table.
How to use relationship for this backend? and witch relationship should use (SQL server backend or MS access frontend) for this backend?
Thank you.
You can create constraints on in the SQL Express database backend. They will be automatically recognized in your linked tables (ODBC). When you create a table, you can add something like:
Create Table Books
(
BookID int not null
Identity(1,1)
Primary Key Clustered,
BookTitle nvarchar(255),
AuthorID int,
.
.
BookDescription nvarchar(max),
Constraint fk_author Foreign Key(AuthorID) References Authors(AuthorID)
);
You can fine tune relationships in any way you like; very powerful.
@@seanmackenziedataengineering Thank you very much.
Thank you for the video. What would be the problem with hyperlink fields? They are very convenient Access, mostly because you can drag and drop files and the link generates automatically.
Indeed, they can be quite useful if you are staying in MS Access. This video is about scaling up to SQL Express which has no hyperlink field type. When you map SQL Express to MS Access with linked tables, you will get a short or long text as an Access datatype when you link (not a hyperlink datatype). Though there are some neat features for hyperlinks in Access, the datatype itself stops you from scaling and can introduce issues or keep you in Access without scaling up. Better to stick to standard datatypes and introduce cool functionality yourself. For example you could have all of your links on a server in text but then refresh a local Access table with frequently used hyperlinks in a hyperlink field when the user logs in. Or, use the cool drag and drop functionality to create links on a local table then just add those to the remote table after adding.
@@seanmackenziedataengineering Interesting suggestion. Thank you.
awesome, how to open the sql server table with ms access which is on another machine
Great idea for a video - I'll put that in my list! The connection method is the same as here th-cam.com/video/vnlqZcgRLm8/w-d-xo.html except using a local address in your connection string. Also, you must open a bunch of ports through Windows Firewall for it to work. 1433, 1434, 135 off the top of my head. Also you need to enable TCP and named pipes in SQL Manager before it will accept new connections.
Always require SQL user and password to open a database to connect
how do I register and then no needed for future ?
Regards ...
The best way is to use the SQL user and password as the actual user and password for your application. Then users have only one login each time. The table links will not store your user and password information.
Can you make a tutorial about it and nit anymore require user and password?
thanks
You're welcome!
Hello sir,
Your video is very good explanation,
I can't display image on my MS Access form, MS Window 11
How to get Varbinary(MAX) image form (.net) SQL server on MS Access form (OLE Object ), Window 11
Thank you.
Great topic for a video! I did this a long time ago - I'll try to remember it and post back here.
may i use ms access sql server database i.e located on internet not the local sql server ?
Yes you can. To do it, you will need to open some ports through your firewall to your SQL Server, or alternatively, use a VPN. The latter is more secure. This is essentially what Azure SQL is, SQL Server on the internet.
Thanks for your quick reply and do you have any recorded lecture on this topic@@seanmackenziedataengineering
@@rhinoteksuhail7425 not yet but great idea for a video!
10/10
Glad you enjoyed!