Title pretty much says it all. We're forced to use 15 year-old software in college, and I don't have the time to learn a piece of software on my own, not to mention how slow it'd run on the computers in college. VB6 itself barely runs.
Now, we were barely taught to code, with the first language we were taught being Pascal, and now VB6 (bearing in mind this is 2015!).
So, with barely any knowledge and what little information I could find on the internet, I tried this horrible line of code:
frm_menu.dbMain.Execute "if(exists(select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME != 'fencer' )) begin create table fencer(<insert fields here>) end"
frm_menu being the form the database variable called dbMain is located. 'fencer' is the name of the table.
Of course, the <insert fields here> I have just added because I have a massive amount of fields, but the syntax is fine and has been tested.
I haven't a clue what information_schema is, and research doesn't help and I don't have too much time to try and understand it.
VB6 throws the following error:
Runtime Error '3078' The Microsoft Jet Database Engine cannot find the input table or query (line >of code stated above). Make sure it exists and its name is spelled correctly.
Unfortunately, being taught by such incompetent teachers has led to me being in the dark with no time to fix it.
If anybody could provide any advice or help I'd be greatly appreciative.
Thanks.
The piece of code you posted tries to select the table name from the database. What you have posted though is not quite correct. The != mean not equal to. The ! is known as a "Bang" and as far as SQL is concerned is syntactically the same, (and interchangeable with), as <>. The code as posted will select all the tables in the database where the name is not "fencer".
What the sample you posted is doing is passing a query that tries to select the table information from the database, and if nothing is returned create the table. If you run that line of code before you try to access the database it will check for the existence of the table in the database, and if it is not found, it creates the table.
The corrected code should be (SQL):
frm_menu.dbMain.Execute "IF (NOT EXISTS( SELECT * FROM " & _
INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'fencer'))" & _
"BEGIN" & _
code to create the table goes here
"END"
The corrected code should be (MS Access):
frm_menu.dbMain.Execute "If Not Exists(SELECT [Name] FROM MSysObjects WHERE " & _
"[Name]='spencer' And Type In (1,4,6)) Then " & _
code to create the table goes here
"End If"