AL: Using SQL Server with Access, by Maria Barnes

Maria Barnes speaks on “Using SQL Server With Access” and covers different ways of connecting to SQL Server from Access including Linked Tables, DSNLess Connections, Recordsets, Pass thru Queries, and Stored Procedures.
Database:
accessusergroup...
Presentation:
accessusergroup...
SQL Converter
accessusergrou...
7:02 linked tables are the most common way to link Access and SQL
7:36 benefits to use SSMA to help migrate tables from Access to SQL Server
SSMA = SQL Server Migration Assistant
8:22 match SSMA bitness to office
10:43 SQL Datasets
11:08 Adrian explain why SQL Server uses 1 and 0 for Yes and No (it's in the bits)
11:26 SQL Server has a bit field
11:32 Access stores boolean in integer
11:47 every single bit a 1 in binary is minus 1 in decimal
12:00 every bit 0 is 0 in decimal
12:13 Some developers use an integer field.
12:37 link tables: External Data, New Data Source, From Database, From SQL Server
13:00 link doesn't store DSN name, stores DSN information.
Hover over table to see connection string
13:30 Table Design - Description property
13:59 what the DSN file looks like?
set up SQL Server directly through Get External Data interface as well.
17:10 look at linked tables in Access
17:26 SSMS
SSMS = SQL Server Management Studio
18:38 DSNLess connection
19:31 VBA explicitely control connection
19:43 ADO connection accessible publically
20:03 open ADO connection in Form Open event
21:15 purpose for variable names to help protect code
22:00 obfuscate
22:37 Form that opens when database is opened, calls OpenC
23:11 data source (RecordSource) of form is a linked table
23:52 In form design, the RecordSource is blank.
24:06 Open event, make a call to routine called ADO_Form.
Pass form itself and a Select statement
24:41 VBA for ADO_Form called on Open to populate a form with data.
24:54 SQL_Connection function
25:20 having an intermediate function is safer in case you lose internet
25:58 set form RecordSet to that recordset, close recordset in this function, and set to nothing.
26:30 what you can do with SQL datasets
26:56 call to stored procedure with parameters inline
27:06 VBA Form_Open SQL Select statement to run server-side
27:26 question about calling sp
27:36 commented line below shows calling a stored procedure
29:20 dbSeeChanges
30:02 question: does dbSeeChanges have adverse effects if the tables are in Access?
30:54 Queries
33:34 can see degradation running Access queries through linked tables.
33:58 Pass-through queries
35:15 error running server SQL when form opens
36:03 use SQL converter on AccessUserGroups.org
38:26 Adrian suggestion to use IN clause of SQL to connect to external database
39:05 example SQL using IN
43:45 Adrian example IN clause - end ' should be before ;
44:15 make pass-through query in VBA
44:17 VBA make a new querydef
46:41 Stored Procedures - SQL Command object
47:30 VBA CallSQLStoreProcedure
47:53 CommandText is stored procedure name
48:05 RETURN_VALUE parameter
50:25 Resources
54:31 George says Pass-Through Queries are read-only
55:05 a subform can fill with data first, problem if parent filter needed first
55:30 unbound subform, bind when parent form loads
56:52 AUG lunch videos have a 4-part series on basics of Access for beginning developers, links below
57:30 Recordsource is set via code
57:41 also use to load subforms on tabs
58:48 Returns Records property
59:26 updateable query vs action query
1:00:15 use linked table to be write-able
1:01:27 George, Colin's video Edit Linked Excel files in Access (link below)
1:01:38 Adrian, can update records using IN
1:01:57 Next meeting Text-to-speech, TTS
Video links *
Access Lunchtime playlist
• Access Lunchtime
SQL Server with Access playlist
• Access with SQL Server
SQL Server Academy: Introduction to SQL Server, by Juan Soto (54m29)
• SQL Server Academy--In...
Access for Beginning Developers 4-part series, by Maria and crystal (strive4peace)
1. Access overview and shortcut keys (54m03)
• AL: Tips & Shortcuts f...
2. Forms, Reports, Macros, QAT (52m16)
• AL: Beginning Access D...
3. Queries (57m12)
• AL: Part 3 - Queries! ...
4. VBA and Macros (1h00m27)
• AL: Beginning Access D...
Edit Linked Excel files in Access, by Colin Riddington, isladogs (10m50)
• Edit Linked Excel file...
Next!
Tuesday SEPTEMBER 27, 2022, at 12 noon Central time
Using Text-To_Speech TTS in Access, by David Nealey and Alessandro Grimaldi
accessusergrou...
AccessUserGroups.org
Lunchtime chapter host Maria Barnes
Last Tuesday @ noon Central time CT -- join us!
accessusergrou...
✓ Join our free online Access User Groups

[@LearnAccessByCrystal]

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.

[@jacksonmacd]

Well done. Thank you. I wish I had seen this about two years ago, it would have saved a lot of work.

[@amilmotitalib348]

Appreciate and thanks for the great share Ms. MB! hoping you will also give a crank on ACCESS and SQL SERVER security.

[@mariabarnes6376]

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

[@kentgorrell]

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.

[@mariabarnes6376]

Good point and good practice, Kent, to plan ahead for the future and make your code more portable :)

[@kentgorrell]

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

[@mariabarnes6376]

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.

[@classmsystems7343]

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.

[@mariabarnes6376]

There are links to the presentation and the sample database in the KZbin description above if you expand it.

[@kentgorrell]

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.

[@HeyTezza]

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?

[@mariabarnes6376]

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.

[@HeyTezza]

@@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 :)

[@JoeSmith-ez3zg]

Has an example db been posted anywhere yet?

[@mariabarnes6376]

Joe, not sure what type of example you are looking for but there are links to the presentation and the sample database in the KZbin description above if you expand it.

[@hasanmougharbel8030]

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.

[@AccessUserGroups]

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!

[@kentgorrell]

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,

[@kentgorrell]

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?

[@tjeffryes1]

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?

[@kentgorrell]

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.

[@tjeffryes1]

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

[@kentgorrell]

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

[@AccessUserGroups]

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/

[@MakaraMeng-c3i]

Hello! Can I Join Access User Groups to learn more?

[@shailendrasoliya]

Hii! If i need to add employee photo field how can i do with sql server and ms access form

[@kentgorrell]

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.

[@kentgorrell]

but i ryou use a PT the sub form will hot be mutable.

[@kentgorrell]

PT recordsets are NEVER updateable. You need to use a linked table or a View.

[@kentgorrell]

or an ADO recordet