One of the problems in posting example databases is that as access evolves, the example format might not be supported. The reverse is true, you might post a modern example and find that some people can't use it because they use an older version of MS Access.
There are various solutions to this problem but the one I am interested in is taking a table and using VBA code to deconstruct that table into a set of VBA instructions.
The table can then be recreated in any any MS Access version. I've never seen anything like that, so I'm wondering if anyone knows of anything like it, or can suggest a suitable Google search that might ferret out the code.
Thanks for your reply Ranman. I'm wanting to save the actual structure of the table as like text instructions, not the data, although I will need the data to put back in the example.
Yes, that's what I'm thinking Minty, but doing the reverse. I want to take a table and destructure it into text instructions. Then recreate it with "Create Table in SQL.
I've never seen anything like it. It seems so possible and I'd be surprised if no one's done it.
I think I've seen somewhere something that looked into the sys.tabledef or similar. I'm guessing it must be possible, in fact the SQL upsizer must do this in that sort of fashion.
The most convenient text instructions to record would be the actual SQL commands to recreate the table.
An easy way to see what these command look like would be to use the Script To New Table option on a table in SQL Server Management Studio. This of course is on an SQL server table but the commands in Access are much the same.
Upsize the Access table and there you have it, assuming you have SSMS.
Hi Galaxiom, thank you for the tip I will have a look.
I was looking through some old posts of yours earlier today:-
I made these Notes whilst I was working on it:- ================================================
2016/04/12 12:08
This Thread ( Galaxiom) http://www.access-programmers.co.uk/forums/showpost.php?p=1096966&postcount=5
Appears to be a good example of how to put an SQL statement together in other words, I mean two SQL statements inside one.
I'm thinking create the main SQL statement to return all of the records for that particular user? But hold on I don't think that will work if it's a group by SQL statement? scratching head having a think..
That gave me the clarity I needed. I love the layout. I find that a good lay out, a logical ordered structure, is one of the best ways of working, it helps you remember, it helps you design, it's tidy and well ... just right feels right...
NOTES END ================================
I was trying to create a complicated SQL query. I found 3 of your posts which had some excellent examples, and now I have my query working so thank you for your help!
By the way I am making notes because I hope to do a blog about building SQL statements at some stage. I've been Gathering notes and things for a few months now ready to sit down and write it all up and maybe do a couple of videos.
My YouTube videos don't generate much income however I have noticed something called patron, https://www.patreon.com/ I'm thinking of signing up with them and seeing if that will generate more income. I want to leave the work side of MS Access and get on with the stuff I really like doing, which is showing people how to do stuff!
In the examples you showed of my code, I have varied how I handle the parentheses, sometimes putting them on the same line as code and others on a separate line. More recently I have been using the two variations to mean different things.
Access SQL gets very messy with the need for nesting multiple joins. I separate these from the subquery parentheses. The following is a trivial example to illustrate.
Code:
SELECT *
FROM table1 AS tA ((
INNER JOIN
(
SELECT *
FROM Table6 AS t6
INNER JOIN
(
SELECT *
FROM Table3 AS t3
INNER JOIN table4 AS t4
ON t3.field = t4.field
) AS qX
ON t6.field = qX.field
) AS qB
ON tA.field = qB.field
)
INNER JOIN table2 AS tC
ON tA.field = tC.field
)
INNER JOIN table5 AS tD
ON tA.field = tD.field
As the joins nest deeper I keep adding the open parentheses on the same line and close them on separate lines.
A subquery close parenthesis will always be followed by AS so they are distinct from the join nesting closers. A subquery open parenthesis is always on a separate line. The nesting open parenthesis are easily counted and the matching closes easily found.
I use mixedCase as the table aliases with "t" and "q" as prefixes. This separates them from the key words that I always use UPPER CASE and the objects which I normally use CamelCase. (I didn't here because I like to use use lower case as placeholders in notional code.)
I use a meaningful abbreviation of the tablename as an alias. Similarly on the next level if I can get an meaningful abbreviation for what the query represents. However when it get really complex with multiple levels and subqueries per level I use letter ranges to match the higher query/subquery levels.
I do much prefer writing queries for TSQL where nested joins are not required, and especially in SSMS where Intellisense is supported. And of course where the app does not screw with the code like Access.
When I am writing complex code in Access I will append a line to the end of the query. This is an example used for a query that returns three fields. Change the number of Nulls to match however many fields are returned by the real query. Dummy is just an empty table.
Code:
UNION ALL SELECT Null, Null, Null FROM Dummy
Access Designer can't handle Union queries so it leaves the SQL alone. The query is definitely not updateable but this is almost invariably the case on complex queries anyway.
My formatting is just one way. There is no objective "best way" so long as one is consistent.
