0

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.

jarlh
  • 42,561
  • 8
  • 45
  • 63
Polyrogue
  • 13
  • 6
  • You need to add a tag for the specific database your using for this to be answerable – Alex K. Dec 03 '15 at 11:19
  • you would also want `if not exists (select .... where TABLE_NAME = 'fencer')` as there will be existing tables *not* called fencer – Alex K. Dec 03 '15 at 11:19
  • If you are dealing with MS Access, then information_schema queries are not in use. See here: http://stackoverflow.com/questions/2985513/check-if-access-table-exists – Arvo Dec 03 '15 at 11:23
  • Split your actions: first execute a "select count(*) from table". catch the error, if table doesn´t exist, then execute your "create table" statement. – nabuchodonossor Dec 03 '15 at 11:39
  • Thanks for the comments. nabuchodonossor, could you tell me how to do try and catch statements? Thanks a lot all. – Polyrogue Dec 03 '15 at 12:24

2 Answers2

1

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"
jac
  • 9,666
  • 2
  • 34
  • 63
0

for mysql

a = conn.Execute("select COUNT(*) as Count from INFORMATION_SCHEMA.TABLES where TABLE_NAME = 'fencer' ")

If a!Count = 0 Then

  conn.Execute "code to create the table goes here"

End If
Toto
  • 89,455
  • 62
  • 89
  • 125
Amit Kabadi
  • 11
  • 1