Hi Sean - Question: Some of my Access queries have criteria parameters that reference a control via the [Forms]![FormName]![Control] method… when I attempt to migrate queries using the “bang” reference, I get red squigglies and error messages. Any guidance here? Thanks!
Those parameters will not work in SQL Server, but there are a couple of techniques for that case. It depends on if your queries are nested. If no, just use the linked odbc tables and keep your existing Access query (it should work "as is" with Forms! etc). The parameter should work. If you have "queries using other queries" that you moved over to SQL Server, you can remove the parameters in the migrated query on SQL Server (make one big unfiltered result) then ODBC-link the result. You can then make an Access query with Forms!MyForm!MyControl etc. and SQL Server will still be very fast, if not faster than Access. Maybe we can review on Discord when we get a chance! I'll look at my schedule.
@seanmackenziedataengineering thank you for saving me time… I was wondering if i just needed to find the syntax but helpful to know it makes more sense to find a workaround. I’ll definitely stop by Discord this weekend - hope to catch you! Thanks as always
Hey Sean, I’ve just finished moving all my data over now I’m rebuilding the views, I’ve got decimal(12,6) for some columns, when I’m summing these in the view it’s converting it in the linked tables to short text. I’ve tried cast and convert but I’m having no luck, any ideas? I don’t need 6 decimals if that’s the issue?
@@seanmackenziedataengineering oh thank you so much, I was casting and converting to decimal, didn’t think of float, that’s worked. Interestingly I created another column with the same info with a float type, summed that and it didn’t need to be converted, so I think I’m going to change as my decimal fields to float. I also found I needed to add in a Row Version timestamp field. There were a couple of times my records would be locked after I updated, that seems to have fixed that (I think), thank you again I’ve been looking for info everywhere :)
@@seanmackenziedataengineering oh so glad to hear you say that, I was hoping I was doing the right thing, thank you. I’ve been able to do this whole project with your videos so they’re great!
If you mean "data entry into queries" then this will depend on how complex the query is. Queries (views) become "non-updatable" at a certain point, so you have to design another way that is simpler, like a view on a table with simple criteria and no joins. If you mean data entry in general, then the answer is: your Linked Tables in Access operate almost exactly like native tables, so you can design your data-entry forms in the same way. You can see it here: ruclips.net/video/vnlqZcgRLm8/видео.html Good question, thanks!
9:15 Pre-migration slowness
25:26 Post-migration awesomeness
Sean, I’m finally at the point where I can truly apply the content here. So helpful, thank you!
That's great! Query migration will "level up" your app. Pretty cool when you see it in action.
Commenting for the algorithm. Appreciate yet another interesting topic!
Much appreciated! This is a good optimization topic.
Hi Sean - Question: Some of my Access queries have criteria parameters that reference a control via the [Forms]![FormName]![Control] method… when I attempt to migrate queries using the “bang” reference, I get red squigglies and error messages. Any guidance here? Thanks!
Those parameters will not work in SQL Server, but there are a couple of techniques for that case. It depends on if your queries are nested. If no, just use the linked odbc tables and keep your existing Access query (it should work "as is" with Forms! etc). The parameter should work. If you have "queries using other queries" that you moved over to SQL Server, you can remove the parameters in the migrated query on SQL Server (make one big unfiltered result) then ODBC-link the result. You can then make an Access query with Forms!MyForm!MyControl etc. and SQL Server will still be very fast, if not faster than Access. Maybe we can review on Discord when we get a chance! I'll look at my schedule.
@seanmackenziedataengineering thank you for saving me time… I was wondering if i just needed to find the syntax but helpful to know it makes more sense to find a workaround. I’ll definitely stop by Discord this weekend - hope to catch you! Thanks as always
Hey Sean, I’ve just finished moving all my data over now I’m rebuilding the views, I’ve got decimal(12,6) for some columns, when I’m summing these in the view it’s converting it in the linked tables to short text. I’ve tried cast and convert but I’m having no luck, any ideas? I don’t need 6 decimals if that’s the issue?
Interesting.. what did you cast/convert to? You could try cast as float on the server side (iirc this will translate to a double column in Access)
@@seanmackenziedataengineering oh thank you so much, I was casting and converting to decimal, didn’t think of float, that’s worked. Interestingly I created another column with the same info with a float type, summed that and it didn’t need to be converted, so I think I’m going to change as my decimal fields to float. I also found I needed to add in a Row Version timestamp field. There were a couple of times my records would be locked after I updated, that seems to have fixed that (I think), thank you again I’ve been looking for info everywhere :)
@@HeyTezza hey that’s great, I’m glad it worked! Access with DAO/ODBC generally works better with floats/double data types over decimals.
@@seanmackenziedataengineering oh so glad to hear you say that, I was hoping I was doing the right thing, thank you. I’ve been able to do this whole project with your videos so they’re great!
How do you handle scenarios where the users input data into Access to query items in Azure SQL?
If you mean "data entry into queries" then this will depend on how complex the query is. Queries (views) become "non-updatable" at a certain point, so you have to design another way that is simpler, like a view on a table with simple criteria and no joins.
If you mean data entry in general, then the answer is: your Linked Tables in Access operate almost exactly like native tables, so you can design your data-entry forms in the same way. You can see it here: ruclips.net/video/vnlqZcgRLm8/видео.html Good question, thanks!