well done in your standard thorough fashion. Thanks, Maria. Great tips like bind subforms on parent form Load event, which is relevant for databases that aren't linked to SQL Server too. Thanks for sharing your clever and useful code. It was insightful to hear Adrian's explanation of -1 and 0 for YesNo. I often wondered why Access uses -1 and now it makes sense! Question: now that we have the DateTime2 data type, are there properties Access can set to simulate what a TimeStamp does? It was nice to learn what SSMA does to help data migration. Even though Pass-Through queries can't reference local tables, you can make another query using a PT as a source as well as local tables too. Since Select PTs are read-only, I assume the resulting query would also be read-only. What I use a lot is a pass-through query for RowSources and change the SQL. On making Pass-Throughs, if you make a query using a linked table and then choose Pass-Through on the ribbon, Access used to adjust the SQL and fill the connection string ... not so anymore? I found your variable name obfuscation to help safeguard information interesting, and laughed because you couldn't say it! I can relate because I had a problem saying it too.
dbSeeChanges - I always use when opening read/write recordsets this regardless of whether the BE is SQL Server or Access. Makes migration easier when you get there.
Just one clarification here - a SQL Recordset is essentially a window into SQL Server data. Depending on how you setup your recordset, it is either updateable or not. If it is updateable then any updates you make from Access will be sent back to SQL Server. This is true even for Snapshot types. The difference in the types makes a difference mostly in what you can see in Access, like how quick you can see updates on the SQL Server side (not until you get a new Snapshot), it does not mean that you only have a local copy. The only way changes do not get pushed back to SQL is if your recordset is not updateable. And in that case Access form fields will also not be updateable. If you want to do client side editing that does not push back to SQL Server you need to pull data into a local Access table instead.
@Adrian, Access could have done a simple bit but, for reasons that are above my pay grade, they decided to do what you descibed. and what a brilliant explanation of why a true is -1. Thank you. I'm guessing that they wanted a Yes/No/DontKnow. You can't do that with just one bit.
IN works differently when it is use the FROM as opposed to the WHERE clause. In FROM it allows you to access external dbs. In a WHERE, you use IN to filter on specified values.
a bit is exactly that, a bit. A bit can only be a one or a zero. An integer takes up more space, true, but the 'space' is insignificant. I've never found a need to use a small iinteger instead of a bit but I can see how some people may.. Integer allow you to use Triple state -Yes / No / Dont Know. If you use a bit in SQL Server and you don't provide a default, then inserting without explicitly specifying a value will throw an error. Amazingly, if you add a new bit column to an existing table using TSQ and you want to update all exiting records to the default, you can do that,
Importand to remember - zero is always false, anything else is true. Unless it's null, Null is a whole other story. Youll need to use the IsNull, funcion, Nz function or instant iifs to deal wilh nulls. See why ut;s easuer just ot use Bit and provide a default?
Hello man, God bless your efforts. I have a new question as a newbie. How shall i use the server optimizer in sql managemnt studio, is there any inbuilt settings or parameters to optimize the server, or is the server optimizer already configured to work optimally on my machine? Thanks a lot.
Hi Hasan! Unfortunately I'm not an expert on SQL Server, just an expert on Access with SQL Server. But in general, SQL Server is so much more powerful than Access I have not had the need to use the optimizer. Please like and subscribe to our channel! Thanks!
Joe, not sure what type of example you are looking for but there are links to the presentation and the sample database in the UA-cam description above if you expand it.
I'm curious about the concern about security and the connection string being visible in linked tables. Where I work, the Access application is always compiled (ACCDE) and the navigation window is always shut down. Additionally, we also disable the Shift key back door. Is this approach still hackable?
Yep, but you can make it more secure by ensuring that the application is only run in the runtime. The best practice is to use an accde in runtime. Maybe not 100% secure but as secure as you can get. You can either a) only install the runtime version of Access on the users' devices or you can use a shortcut to open the full version in runtime mode. If you want the code to put in your startup to ensure the application can only start in runtime then ask in a reply.
@@kentgorrell Thanks for your response. I was just curious, since Juan Soto declared putting connection strings in linked tables as being "stupid". Since our users use the runtime and compiled versions, I'm still curious why this might be stupid.
@@tjeffryes1 In theory, If you are using Windows Autentification, a user could use the connection string to connect directly to SQL Server bypassing your application. Not a likely scenario, but possible.
Hi Timothy! Thanks for watching the video, I'm afraid even after compiling and disabling special access keys that yes, in theory its still hackable, but only a few around the world would know how, so you might still be good to go. (Unlikely that any of your users are an Access expert). In regards to my comment about saving your credentials in linked tables as "Stupid", (not the best choice of word), it also prevents a user or hacker from obtaining credentials. We will be discussing alternatives in my Access with SQL Server Academy session in December, please join us! accessusergroups.org/sql-server-with-access/
I’m confused with setting up the dsnless connection, so I set up the database first with linked tables using a dsn, then when I give my database to others they can use it with dsnless?
No if you setup the links with DSN then you would need to save that DSN as a File and distribute it to the others who use it. The advantage of a DSN less connection is that it relies on a connection string that does not have the extra DSN file requirement. It is a direct link.
@@mariabarnes6376 oh thank you so much, I’ve literally been looking everywhere for this one bit of info! Your videos are amazing I can’t believe I’ve only just found this channel :)
sub form late bindiing is good, I don'[t understand why sub forms open before the parent. Access tells you that you can't use a PT as the RS oh a sub form but you can, You just can't ue the Master Child preperties. you need to pass a parameter to the PT to filter the PT o the parant's Current event.
Well done. Thank you. I wish I had seen this about two years ago, it would have saved a lot of work.
well done in your standard thorough fashion. Thanks, Maria. Great tips like bind subforms on parent form Load event, which is relevant for databases that aren't linked to SQL Server too. Thanks for sharing your clever and useful code.
It was insightful to hear Adrian's explanation of -1 and 0 for YesNo. I often wondered why Access uses -1 and now it makes sense! Question: now that we have the DateTime2 data type, are there properties Access can set to simulate what a TimeStamp does? It was nice to learn what SSMA does to help data migration.
Even though Pass-Through queries can't reference local tables, you can make another query using a PT as a source as well as local tables too. Since Select PTs are read-only, I assume the resulting query would also be read-only. What I use a lot is a pass-through query for RowSources and change the SQL. On making Pass-Throughs, if you make a query using a linked table and then choose Pass-Through on the ribbon, Access used to adjust the SQL and fill the connection string ... not so anymore?
I found your variable name obfuscation to help safeguard information interesting, and laughed because you couldn't say it! I can relate because I had a problem saying it too.
dbSeeChanges - I always use when opening read/write recordsets this regardless of whether the BE is SQL Server or Access. Makes migration easier when you get there.
Good point and good practice, Kent, to plan ahead for the future and make your code more portable :)
Appreciate and thanks for the great share Ms. MB! hoping you will also give a crank on ACCESS and SQL SERVER security.
I think that would be a nice follow up - good idea. I will try to schedule that for this fall. Glad you enjoyed the presentation
Just one clarification here - a SQL Recordset is essentially a window into SQL Server data. Depending on how you setup your recordset, it is either updateable or not. If it is updateable then any updates you make from Access will be sent back to SQL Server. This is true even for Snapshot types. The difference in the types makes a difference mostly in what you can see in Access, like how quick you can see updates on the SQL Server side (not until you get a new Snapshot), it does not mean that you only have a local copy. The only way changes do not get pushed back to SQL is if your recordset is not updateable. And in that case Access form fields will also not be updateable. If you want to do client side editing that does not push back to SQL Server you need to pull data into a local Access table instead.
@Adrian, Access could have done a simple bit but, for reasons that are above my pay grade, they decided to do what you descibed. and what a brilliant explanation of why a true is -1. Thank you. I'm guessing that they wanted a Yes/No/DontKnow. You can't do that with just one bit.
IN works differently when it is use the FROM as opposed to the WHERE clause. In FROM it allows you to access external dbs. In a WHERE, you use IN to filter on specified values.
Well done Maria. I'm not sure which of the links above allows me to download the sample database. Could you supply me with that information? Thanks.
There are links to the presentation and the sample database in the UA-cam description above if you expand it.
a bit is exactly that, a bit. A bit can only be a one or a zero. An integer takes up more space, true, but the 'space' is insignificant. I've never found a need to use a small iinteger instead of a bit but I can see how some people may.. Integer allow you to use Triple state -Yes / No / Dont Know.
If you use a bit in SQL Server and you don't provide a default, then inserting without explicitly specifying a value will throw an error. Amazingly, if you add a new bit column to an existing table using TSQ and you want to update all exiting records to the default, you can do that,
Importand to remember - zero is always false, anything else is true. Unless it's null, Null is a whole other story. Youll need to use the IsNull, funcion, Nz function or instant iifs to deal wilh nulls. See why ut;s easuer just ot use Bit and provide a default?
Hello man,
God bless your efforts.
I have a new question as a newbie.
How shall i use the server optimizer in sql managemnt studio, is there any inbuilt settings or parameters to optimize the server,
or is the server optimizer already configured to work optimally on my machine?
Thanks a lot.
Hi Hasan! Unfortunately I'm not an expert on SQL Server, just an expert on Access with SQL Server. But in general, SQL Server is so much more powerful than Access I have not had the need to use the optimizer. Please like and subscribe to our channel! Thanks!
Has an example db been posted anywhere yet?
Joe, not sure what type of example you are looking for but there are links to the presentation and the sample database in the UA-cam description above if you expand it.
Hello!
Can I Join Access User Groups to learn more?
I'm curious about the concern about security and the connection string being visible in linked tables. Where I work, the Access application is always compiled (ACCDE) and the navigation window is always shut down. Additionally, we also disable the Shift key back door. Is this approach still hackable?
Yep, but you can make it more secure by ensuring that the application is only run in the runtime. The best practice is to use an accde in runtime. Maybe not 100% secure but as secure as you can get.
You can either a) only install the runtime version of Access on the users' devices or you can use a shortcut to open the full version in runtime mode.
If you want the code to put in your startup to ensure the application can only start in runtime then ask in a reply.
@@kentgorrell Thanks for your response. I was just curious, since Juan Soto declared putting connection strings in linked tables as being "stupid". Since our users use the runtime and compiled versions, I'm still curious why this might be stupid.
@@tjeffryes1 In theory, If you are using Windows Autentification, a user could use the connection string to connect directly to SQL Server bypassing your application. Not a likely scenario, but possible.
Hi Timothy! Thanks for watching the video, I'm afraid even after compiling and disabling special access keys that yes, in theory its still hackable, but only a few around the world would know how, so you might still be good to go. (Unlikely that any of your users are an Access expert). In regards to my comment about saving your credentials in linked tables as "Stupid", (not the best choice of word), it also prevents a user or hacker from obtaining credentials. We will be discussing alternatives in my Access with SQL Server Academy session in December, please join us! accessusergroups.org/sql-server-with-access/
Hii! If i need to add employee photo field how can i do with sql server and ms access form
I’m confused with setting up the dsnless connection, so I set up the database first with linked tables using a dsn, then when I give my database to others they can use it with dsnless?
No if you setup the links with DSN then you would need to save that DSN as a File and distribute it to the others who use it. The advantage of a DSN less connection is that it relies on a connection string that does not have the extra DSN file requirement. It is a direct link.
@@mariabarnes6376 oh thank you so much, I’ve literally been looking everywhere for this one bit of info! Your videos are amazing I can’t believe I’ve only just found this channel :)
sub form late bindiing is good, I don'[t understand why sub forms open before the parent. Access tells you that you can't use a PT as the RS oh a sub form but you can, You just can't ue the Master Child preperties. you need to pass a parameter to the PT to filter the PT o the parant's Current event.
but i ryou use a PT the sub form will hot be mutable.
PT recordsets are NEVER updateable. You need to use a linked table or a View.
or an ADO recordet