Profile

Cover photo
Harvey French
Attended School Of Life
Lives in Essex
22 followers|85,290 views
AboutPostsPhotosYouTube+1's

Stream

Harvey French

commented on a channel on YouTube.
Shared publicly  - 
 
Hi

I like to avoid putting code that changes the state of a an object outside of an object.  I keep coming across forms with code that changes their subforms typically by changing the SQL or filters etc... This breaks OO principals and leads to messy code.  (The subform in effect becomes part of the main form object and it is harder to re-use it).

It took me a while but I found a way to prevent code being structured like this.  I used object variables in the subform to respond to events on the main form.  So Instead of using the subform control's "Link master/child Fields" I used the object variables to respond to changes in the main form.

I think this is a very useful technique.  if you have anything to add could you please add to this question on stack overflow...

http://stackoverflow.com/questions/31611912/how-best-to-call-a-public-sub-routine-declared-in-a-form-used-as-the-source-obje

Harvey French

commented on a video on YouTube.
Shared publicly  - 
 
In case you're not aware, once the form has a recordsource, you can use the ribbon>>Form Design>Add Existing tools button to select fields form the recordsource and drag them onto the form. Then use ribbon>>Form Arrange>>tabular to put the controls into a very useable grid layout.

Harvey French

commented on a video on YouTube.
Shared publicly  - 
 
Thanks again Steve.  I'm following your demos and finding the links you give useful.

Your videos are raising a few questions with me. So I'll shared my thoughts.

With a DSN-Less connection and a project with "No linked DAO tables". 
A.  you can't based a form on a "DAO" querydef or tabledef object (they don't exist!). So you would use the techniques you presented before (ie Form_Open/Close to mane the db and rs  and adding butons to movenext/last etc...)

B. The tabledef objects stored in a backend access database have many properties that help with creating forms quickly (like those that define the combo box properties that are used with a column, default values, validation, etc)   These properties are lost when tables are moved to MSSqlServer.  They are certainty not available in "DSN-Less connections"

C. I can see myself creating a form for every table with controls set up that can be used as templates that have all the properties you would have found in the table (and more and methods etc).  It may be I could created a classes/modules for each table that can be used to set the default properties for the type of control(s) used with a table column... and manage them.

D. No tables defs are available to use to view the data.  So you would use SQL Server to view the data now or QueryDefs......

E. Can I create named QueryDef objects when linked tables do not exist.   I can use these to view the data as necessary..... I suspect so as the querdef object has "Source Connect Str" property and Source databsae property.

Importing data to SQL Server
F. The SSMA - SS Migration Assistant might be a better method to use. It imports queries, indexes, relationships, foreign keys, etc 
[See SSMA ](https://technet.microsoft.com/en-us/library/hh313085(v=sql.110).aspx)

G. What happens to any access table triggers?  Do they get imported to SQL Server by the 2010 Wizard or by the import/export method you demoed or by the SSMA?

H. If you think you might have to move access data to SQL server, it's best to use SServer from the beginning....

Harvey

Harvey French

commented on a video on YouTube.
Shared publicly  - 
 
This is a very useful introduction and very helpful.  If you have any useful links it would be great if you could share them at the end or in the youtube video notes.

It is clear that the Access 2010 upsizing wizard should be used if possible and the following links expand on the reasons you gave.  

Of particular note in the first link is how the wizard handles the following (as I think the method you present may not give the same results):  
- converting Access field names to legal SQL Server field names
- Validation rules (triggers are created),
- Default Values (er, it does something I don't understand yet.),
- the table properties "Description, Caption, Format, InputMask, and DecimalPlaces." which I think may "get lost" using the method you demonstrate.
- why it gives PK indexes a name beginning with "aaaaa" (As Access chooses the index to use based on the alphabetical order of the index name - which I find hard to believe)

Both links are about olrder version of Access, so they may be dated, however, there doesn't seem to be more recent versions of these articles out there. The second link, in particular, gives some good info but gets pretty heavy, however, I found it useful to better understand issues that can arise.

Links:
1  [Move Access data to a SQL Server database by using the Upsizing Wizard](https://support.office.com/en-ca/article/Move-Access-data-to-a-SQL-Server-database-by-using-the-Upsizing-Wizard-5d74c0df-c8cd-4867-8d07-e6e759d72924?ui=en-US&rs=en-CA&ad=CA&fromAR=1)

2 [Optimizing Microsoft Office Access Applications Linked to SQL Server](https://msdn.microsoft.com/en-us/library/bb188204.aspx)

I also read this about Connect strings, which intrigues me:
In a module, you can define a formatted connect string that specifies connection information. A connect string passes the connection information directly to the ODBC Driver Manager, and it helps simplify your application by removing the requirement that a system administrator or user first create a DSN before using the database.

Thanks for the massive and contribution you are making to my learning!

I hope this might be useful.

Harvey

Harvey French

commented on a video on YouTube.
Shared publicly  - 
 
That was very useful and I also found the comments and replies below useful.  Thanks.

Harvey French

commented on a video on YouTube.
Shared publicly  - 
 
Use a tool to format you SQL nicely (eg www.sqlinform.com)

Harvey French

commented on a channel on YouTube.
Shared publicly  - 
 
Hi Crystal, 

I like to avoid putting code that changes the state of a an object outside of an object.  I keep coming across forms with code that changes their subforms typically by changing the SQL or filters etc... This breaks OO principals and leads to messy code.  (The subform in effect becomes part of the main form object and it is harder to re-use it).

It took me a while but I found a way to prevent code being structured like this.  I used object variables in the subform to respond to events on the main form.  So Instead of using the subform control's "Link master/child Fields" I used the object variables to respond to changes in the main form.

I think this is a very useful technique.  if you have anything to add could you please add to this question on stack overflow...

http://stackoverflow.com/questions/31611912/how-best-to-call-a-public-sub-routine-declared-in-a-form-used-as-the-source-obje

Harvey French

commented on a channel on YouTube.
Shared publicly  - 
 
Hey Steve.  I've just finished watching pretty much most of your videos.  I took a look for a donate button and couldn't find one....I figured I should at least buy you a drink.   I just wanted to say thanks.  Really good content and I picked up a lot of knowledge, as well as re-assuring myself that I knew a bit more that I gave myself credit for.  

I'd also like to draw your attention to my youtube channel, which has 2 videos, one which tries to educate non technical "excel" users on why normalised data is good and why the MIS system they use is so bad.  It non-technical but show cases PowerQuery and powerPivot well.  

If you know anyone who might benefit from videos about "why to use long format data rather than wide format data with pivot tables" my channel might help them.  Here's the more advanced one: https://www.youtube.com/watch?v=SniAP84Kj2c.  

It's main purpose was to trash a system used by 20,000 schools here is the UK, where they (CAPITA) really cocked up the database design.  (eg A grade/mark entered for a pupil's subject is not linked to a subject table or to a table that says what type of grade it is. Shocking.)  This is why schools here use wide format so much.  Long format data simply isn't available, and hence the video...

I'm glad I'm not in education anymore!

Thanks again

Harvey

Harvey French

commented on a video on YouTube.
Shared publicly  - 
 
It's worth knowing that you can right click on the canvas then choose "Show Relationship Labels"

Harvey French

commented on a video on YouTube.
Shared publicly  - 
 
Yet another very useful video.  Thanks.  Other languages and environments offer better error handling features and VBA error handling is very basic.  I came across this paid for suite of classes for Access that massively enhances the way errors can be handled.  Key benefits are:

- It simplifies all you code
-    Allows you to identify the source procedure and module name where the error occurred
-   List the exact line number to identify the line of code that failed
-   Report the full callstack that led up to the exception
- etc..

See  [vbWatchdog](http://www.everythingaccess.com/vbwatchdog.asp)

I've not used it but it is stable and reliable

Harvey

Harvey French

commented on a video on YouTube.
Shared publicly  - 
 
Many Thanks. Useful technique.  Here's a couple of trivial tips for you http://1drv.ms/1ftNEMe    and a link to a VBA addin that I really rate (free trial). http://1drv.ms/1Iu6T5B    It provides more VS like features to the VBA environment.

Harvey French

commented on a video on YouTube.
Shared publicly  - 
 
A very clear video, however, I would recommend that the code which builds a SQL statement for a subform is declared in a public sub routine of subforms code module.

This encapsulates the code relating to the subform in the subform.

Any custom properties, methods and functions you delcare in the subform module can be referenced from the main form using:

Me.MySubFormControl.Form.MyProperty
Me.MySubFormControl.Form.MyMethod
Me.MySubFormControl.Form.MyFunction(P1,...)

eg In the subform you could use a method:

Public Sub CreateSQL(ProductID as variant) 

    me.recordsource = "SELECT ...."   

End Sub

note that I have just opened a question on stack overflow, which further explores this issue.  (In the past people have said, use C#, Use VB, stop using VBA, but I think there is a call for access to behave itself better, so I'm asking again, but differently!)

http://stackoverflow.com/questions/31606432/the-ms-access-subform-control-intellisense-does-not-list-all-form-class-members

I'd value you comments there.

Harvey
Story
Introduction
I sing Baritone on Decani, I cycle a lot and race occasionally.  I swim more.  I used to like running until I got an injury.  I used to prefer computers to people, but now I'm mostly indifferent. :-)
Bragging rights
Half IronMan completed in 7hours 30mins, after doing the swim section in butterfly. Can also sing a high F on a good day.
Education
  • School Of Life
  • Staffordshire University
    Technology Management BSc (Hons)
  • Swansea University
    Physics, Comuter Science, Maths
  • Walthamstow College
    A Levels: Physics, Pure Maths, Statistics
  • Buckhurst Hill C.H.S
    GCSES
  • Staple Road Primary School
    People!
Basic Information
Gender
Male
Other names
Half FLIronMan French
Work
Occupation
Data Management, System development and esign
Places
Map of the places this user has livedMap of the places this user has livedMap of the places this user has lived
Currently
Essex
Previously
Essex - Essex
Links
Harvey French's +1's are the things they like, agree with, or want to recommend.
Business Calendar 2 - Kalender – Android-Apps auf Google Play
market.android.com

Business Calendar 2 ist der Nachfolger unserer vielfach ausgezeichneten Kalender-App.Feature-Übersicht: ▪ Terminplaner und Aufgabenverwaltun

2048
market.android.com

The 2048 app is a fun, addictive and a very simple puzzle game. Join the numbers and get to the 2048 tile!Smallest size app for 2048!HOW TO

The Independent
market.android.com

This is the free official App for users who take their news seriously - from The Independent and Independent on Sunday, titles that are inte

RHYTHM CAT learn to read music
market.android.com

Learn More at: MelodyCats.com Put your rhythm skills to the test! Above all this game is highly entertaining with a great soundtrack to play

Top Sailor sailing simulator
market.android.com

Take Top Sailor with you and learn basics of sailing!*** NOTE *** To ensure that this game will work well on your device, first try the free

A Commentary on the Birth of Love
www.youtube.com

So many people have asked us questions about our experience of natural birth at home so we decided to make a special edition of baby Love's

Kate Bush - Hello Earth (lyrics)
www.youtube.com

Lyrics: http://easylyrics.org/?artist=Kate+Bush&title=Hello+Earth Thanks for checking out our videos and site!

Humpback Whale Shows AMAZING Appreciation After Being Freed From Nets
www.youtube.com

*** Wake Up World Viewer Special - http://aquaponics.wakeup-world.com. Take control of your what you eat by creating your own organic aquapo