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.
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
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.
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.
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.
Great idea for a video - I'll put that in my list! The connection method is the same as here ruclips.net/video/vnlqZcgRLm8/видео.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.
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.
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.
Yaaaaasssss! HEY, almost 10k!!!! Congrats!
Thanks I'm getting there! cheers
Thanks for a very good explanation of sql link table on remote sql server
You are welcome!
Great video, thanks so much.
Glad it was helpful!
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.
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?
Thanks for your video
Most welcome!
Pls can you show us vb script to MS access to MySQL
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.
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!
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.
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?
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 ruclips.net/video/vnlqZcgRLm8/видео.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.
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!
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.
thanks
You're welcome!
10/10
Glad you enjoyed!