Using an Array in SQL Statement

[mrmmickle1]

I am trying to create multiple tables that all have the same fields. I have tried to use an array but it seems like it won't accept it this way. My code looks like this:

[COLOR=#0000ff]Dim[/COLOR] i [COLOR=#0000ff]As Long[/COLOR]
[COLOR=#0000ff]Dim [/COLOR]TableArr [COLOR=#0000ff]As Variant[/COLOR]

TableArr = Array("All", "Genetics", "Reprep", "RapidFire", "Quest_SendOut", "Needs_Screening", "Needs_Data", "Clerical_Review", "Compliance", "Other")

[COLOR=#0000ff]  Set [/COLOR]cnt = CreateObject("ADODB.Connection")
   [COLOR=#0000ff] With[/COLOR] cnt
[COLOR=#008000]        'Creating connection with database[/COLOR]
        .Open dbConnectStr
        
[COLOR=#008000]        'Create All Tables[/COLOR]
          [COLOR=#0000ff] For [/COLOR]i =[COLOR=#0000ff] LBound[/COLOR](TableArr)[COLOR=#0000ff] To[/COLOR] [COLOR=#0000ff]UBound[/COLOR](TableArr)
         
                strSql = "CREATE TABLE " & TableArr(i) & " ([MR_Num] TEXT(20)"
                strSql = strSql & vbLf & ", [Chart_Number] TEXT(12)"
                strSql = strSql & vbLf & ", [Last_Name] TEXT(25)"
                strSql = strSql & vbLf & ", [First_Name] TEXT(25)"
                strSql = strSql & vbLf & ", [Date_Received] DATETIME"
                strSql = strSql & vbLf & ", [Sales_Rep] TEXT(10)"
                strSql = strSql & vbLf & ", [Hold_Reason] TEXT(200));"
[COLOR=#ff0000]                .Execute strSql 'I get a Syntax error in CREATE TABLE statement Error Message on this line[/COLOR]
        
        [COLOR=#0000ff]    Next [/COLOR]i
        
        .Close
        
[COLOR=#0000ff]    End With[/COLOR]

I know that the error has to do with the array because when I change the SQL Statement to this it will execute fine:

[COLOR=#0000ff]With [/COLOR]cnt
 .Open dbConnectStr
 strSql = "CREATE TABLE TEST_TABLE ([MR_Num] TEXT(20)"
                strSql = strSql & vbLf & ", [Chart_Number] TEXT(12)"
                strSql = strSql & vbLf & ", [Last_Name] TEXT(25)"
                strSql = strSql & vbLf & ", [First_Name] TEXT(25)"
                strSql = strSql & vbLf & ", [Date_Received] DATETIME"
                strSql = strSql & vbLf & ", [Sales_Rep] TEXT(10)"
                strSql = strSql & vbLf & ", [Hold_Reason] TEXT(200));"
                .Execute strSql
      .Close
        
[COLOR=#0000ff]    End With[/COLOR]

Can I not use an array? Do I have do make the tables all separately???

 

[mrmmickle1]

It appears that the reason my code did not function is because my TableArr() incorporated the word "All" which is apparently a reserved word in SQL. It seems strange though because I had a sample database and it had a Table named "All" in it. Not sure why it wouldn't take it in VBA....but when I changed the Table name to "AllSamples" It seems to work fine.

Does anyone know the reason behind this? Any further explanation would be great.


This code works fine now:

[COLOR=#0000ff]Private Sub[/COLOR] CreateDatabaseAndTables()

   [COLOR=#0000ff] Dim [/COLOR]dbConnectStr           [COLOR=#0000ff] As String[/COLOR]
    [COLOR=#0000ff]Dim[/COLOR] Catalog            [COLOR=#0000ff]     As Object[/COLOR]
[COLOR=#0000ff]    Dim [/COLOR][COLOR=#000000]cnt            [/COLOR][COLOR=#0000ff]         As Object[/COLOR]
    [COLOR=#0000ff]Dim[/COLOR] dbPath                [COLOR=#0000ff]  As String[/COLOR]
 [COLOR=#0000ff]   Dim[/COLOR] strSql                [COLOR=#0000ff]  As String[/COLOR]
[COLOR=#0000ff]    Dim[/COLOR] strDBName               [COLOR=#0000ff]As String[/COLOR]
    [COLOR=#0000ff]Dim[/COLOR] TableArr()              [COLOR=#0000ff]As Variant[/COLOR]
[COLOR=#0000ff]    Dim [/COLOR]i                      [COLOR=#0000ff] As Long[/COLOR]
    
    TableArr = Array("AllSamples", "Genetics", "Reprep", "RapidFire", "Quest_SendOut", "Needs_Screening", "Needs_Data", "Clerical_Review", "Compliance", "Other")
    strDBName = "PendingLog_" & Format(Date, "MM.DD.YYYY") & ".accdb"
    dbPath = GetDBPath & strDBName
[COLOR=#0000ff]    On Error Resume Next[/COLOR]
    [COLOR=#0000ff]Call[/COLOR] SetAttr(dbPath, vbNormal)
  [COLOR=#0000ff]  Call [/COLOR]Kill(dbPath)
  [COLOR=#0000ff]  On Error GoTo 0[/COLOR]: Err.Clear
    
    dbConnectStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbPath & ";"

[COLOR=#008000]    'Create new database[/COLOR]
   [COLOR=#0000ff] Set [/COLOR]Catalog = CreateObject("ADOX.Catalog")
    Catalog.Create dbConnectStr
   [COLOR=#0000ff] Set [/COLOR]Catalog = Nothing
   [COLOR=#0000ff] Call[/COLOR] SetAttr(dbPath, vbNormalNoFocus)

[COLOR=#008000]    'Connect to database [/COLOR]
    [COLOR=#0000ff]Set[/COLOR] cnt = CreateObject("ADODB.Connection")
     [COLOR=#0000ff]   With [/COLOR]cnt
           
             .Open dbConnectStr
             
               [COLOR=#0000ff]  For[/COLOR] i = [COLOR=#0000ff]LBound[/COLOR](TableArr) [COLOR=#0000ff]To UBound[/COLOR](TableArr)
[COLOR=#008000]                      'Create Data Tables[/COLOR]
                      strSql = vbNullString
                      strSql = "CREATE TABLE " & TableArr(i) & " ([MR_Num] TEXT(20)"
                      strSql = strSql & vbLf & ", [Chart_Number] TEXT(12)"
                      strSql = strSql & vbLf & ", [Last_Name] TEXT(25)"
                      strSql = strSql & vbLf & ", [First_Name] TEXT(25)"
                      strSql = strSql & vbLf & ", [Date_Received] DATETIME"
                      strSql = strSql & vbLf & ", [Sales_Rep] TEXT(10)"
                      strSql = strSql & vbLf & ", [Hold_Reason] TEXT(200)"
                      strSql = strSql & vbLf & ", [Pending_Days] INTEGER"
                      strSql = strSql & vbLf & ", [Notes] TEXT(200));"
                      .Execute strSql
                 
                [COLOR=#0000ff] Next[/COLOR] i
                                           
             .Close
              
[COLOR=#0000ff]        End With[/COLOR]

[COLOR=#008000]    'Releasing Memory[/COLOR]
    strSql = vbNullString
    dbConnectStr = vbNullString
[COLOR=#0000ff]    Set [/COLOR]Catalog = [COLOR=#0000ff]Nothing[/COLOR]
   [COLOR=#0000ff] Set[/COLOR] cnt = [COLOR=#0000ff]Nothing[/COLOR]
    dbPath = vbNullString
    strDBName = vbNullString
    [COLOR=#0000ff]Erase[/COLOR] TableArr
    i = [COLOR=#0000ff]Empty[/COLOR]

[COLOR=#0000ff]End Sub
[/COLOR]

 

[xenou]

I could speculate for you that the problem with the All reserved word happens when you are working with the ACE library (ADO). In native Access you are more likely using the DAO library to work with the database. For a test you could try running your create table code in DAO instead of ADO. It's also possible that the native database doesn't run through the vba interpreter so it doesn't trip up for that reason.

I believe that if you create tables using a SQL DDL query as you did, you won't get unicode compression in your text fields. You may want to look at that - compression means that characters that can be stored as one byte would be stored as one byte instead of two bytes (saving a lot of space). All things being equal, unless you must, it would be easier to have a template table you created by hand, and then just copy it. But I can post some sample code -- if I can find it -- if you want to see my attempts at creating tables in code. I believed I based my efforts on this page: http://allenbrowne.com/func-DAO.html Also you will lack indexes on key fields (unless you only want the primary key indexed) and that's also something you want to think about.

You can also, by the way, have all the data on ONE table only. You would only need an extra field to say what type of data. This will make writing queries much easier (one query can access any or all of the data by just using your type of data criterion as a key.

 

[mrmmickle1]

xenou,

Thanks for taking a look at this post. I really appreciate it. As far as Access coding with DAO Library vs. Ace Libarary (ADO) I have to admit that I am unfamiliar with the differences. I do know that the code that I adapted originally referenced the JET engine. The issue I ran into was that while these JET engines seemed to work on my personal computer they would not work on my work computer. The solution was to change the reference. Here is a list of the ones that did not work properly on my work computer:

[COLOR=#006400]'dbConnectStr = "Provider=Microsoft.Jet.OLEDB.3.51;Data Source=" & dbPath & ";"[/COLOR]
[COLOR=#006400]'dbConnectStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbPath & ";"[/COLOR]
[COLOR=#006400]'dbConnectStr = "Provider=Microsoft.Jet OLEDB:Engine Type=4;Data Source=" & dbPath & ";"[/COLOR]
[COLOR=#006400]'dbConnectStr = "Provider=Microsoft.Jet OLEDB;Engine Type=4;Data Source=" & dbPath & ";"[/COLOR]

1. How would I run the database DAO instead of ADO as you mention?
2. I would very much like to learn about the unicode compression you mention. I am eager to learn.
3. I understand why it would be easier to put this all in one table. with the addition of an additional field to specify type. Is there an easy way to do this? I have the below code to transfer data into the database. Once again I cannibalized bits and pieces of code to get this to work. I am not familiar with the difference between the ADO and DAO:

[COLOR=#0000ff] Sub [/COLOR]ADOFromExcelToAccess()

   [COLOR=#0000ff] Dim [/COLOR]cn  [COLOR=#0000ff] As Object[/COLOR] [COLOR=#006400]'ADODB.Connection[/COLOR]
   [COLOR=#0000ff] Dim[/COLOR] rs   [COLOR=#0000ff]As Object[/COLOR] [COLOR=#006400]'ADODB.Recordset[/COLOR]
    [COLOR=#0000ff]Dim[/COLOR] r    [COLOR=#0000ff]As Long[/COLOR]
   [COLOR=#0000ff] Dim[/COLOR] i    [COLOR=#0000ff]As Long[/COLOR]
    
    DBTableArr = Array("AllSamples", "Genetics", "Reprep", "RapidFire", "Quest_SendOut", "Needs_Screening", "Needs_Data", "Clerical_Review", "Compliance", "Other")
[COLOR=#008000]    'Connect to the Access database[/COLOR]
    [COLOR=#0000ff]Set [/COLOR]cn = CreateObject("ADODB.Connection")
    cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; " & GetDBPath & GetDBName
[COLOR=#008000]    'Open a recordset[/COLOR]
    [COLOR=#0000ff]Set [/COLOR]rs = CreateObject("ADODB.Recordset")
        
      [COLOR=#0000ff]  For [/COLOR][COLOR=#000000]i [/COLOR]= [COLOR=#0000ff]LBound[/COLOR](DBTableArr)[COLOR=#0000ff] To UBound[/COLOR](DBTableArr)
        
            rs.Open DBTableArr(i), cn, adOpenKeyset, adLockOptimistic, adCmdTable [COLOR=#008000]'All records in a table[/COLOR]
            Sheets(DBTableArr(i)).Activate
            r = 2 [COLOR=#008000]'Start Row[/COLOR]
            
          [COLOR=#0000ff]  Do While [/COLOR]Len(Range("A" & r).Formula) > 0 [COLOR=#008000] 'Repeat until first empty cell in column A[/COLOR]
           
               [COLOR=#0000ff] With[/COLOR] rs
                    .AddNew [COLOR=#008000]' create a new record[/COLOR]
    
[COLOR=#008000]                    ' add values to each field in the record[/COLOR]
                    .Fields("MR_Num") = Range("A" & r).Value
                    .Fields("Chart_Number") = Range("B" & r).Value
                    .Fields("Last_Name") = Range("C" & r).Value
                    .Fields("First_Name") = Range("D" & r).Value
                    .Fields("Date_Received") = Range("E" & r).Value
                    .Fields("Sales_Rep") = Range("F" & r).Value
                    .Fields("Hold_Reason") = Range("G" & r).Value
                    .Fields("Pending_Days") = Range("H" & r).Value
                    .Update[COLOR=#008000] ' stores the new record[/COLOR]
                    
[COLOR=#0000ff]                End With[/COLOR]
                r = r + 1 '[COLOR=#008000] next row[/COLOR]
                
[COLOR=#0000ff]            Loop[/COLOR]
            
            rs.Close
           [COLOR=#0000ff] Set [/COLOR]rs = [COLOR=#0000ff]Nothing[/COLOR]
        
      [COLOR=#0000ff]  Next[/COLOR] i
        
        cn.Close
      [COLOR=#0000ff]  Set [/COLOR]cn = [COLOR=#0000ff]Nothing
[/COLOR][COLOR=#000000]        r =[/COLOR][COLOR=#0000ff] Empty
        [/COLOR][COLOR=#000000]i =[/COLOR][COLOR=#0000ff] Empty[/COLOR]
        
[COLOR=#0000ff]    End Sub[/COLOR]

4. Thank you for the website reference. I have actually come across a few times in the past few days. It was brought up in another thread I am subscribed to with more of my problems
I have reviewed a few times and tried to use the set Property Function and was unsuccessful.... I think much of my problems lie with me not really having a solid base for Access VBA. I am trying to learn as quickly as I can though. I feel that MS Access is a great tool and would love to be able to use it more often for different projects.
OTHER THREAD IS HERE - TROUBLE SETTING TABBED DOCS

Any advice or help resolving my confusion/issues is much appreciated. If you have any additional reference material or guidance I would be grateful to receive it. Thank you for your time.

 

[xenou]

Hi,
I'll get back to soon. To answer one of your questions briefly, ADO and DAO are both libraries for database access. They "do" many of the same things, but since they are different libraries they have some different syntax and different idiosyncracies. But DAO is the "native language" for Access database access, since it was created specifically with Access in mind, whereas ADO is a more "generic" library for working with many different kinds of databases. I typically use ADO when connecting to SQL Server, Excel, or text files, and I use DAO when connection to Access.

There was a brief period of time where MS declared DAO was going to become obsolete and be replaced by ADO. But that turned out to be false, and the Access team continues to maintain DAO for new versions of Access.

 

[xenou]

Okay, so I stand by what i said but it turns out in the one case of using DDL (Database Definition Language - i.e., Create, Alter queries) it is in fact ADO that works best. So forget about DAO for now.

Here's my try(based on Allen Browne's examples) -- so we get unicode compression and indexes now. In addition to referencing ado you will need to reference ADOX too. On my PC (with office 2010) the libraries I have referenced are: Microsoft ActiveX Data Objects 6.1 and Microsoft ADO.Ext 6.0 for DDL and Security.

Also since I tested right inside of Access I didn't not have to do anything with the connection. You will need to edit this code to open a connection to your database. For the provider, use the ACE provider for newer .accdb access file format. Use ACE or JET for the older .mdb file formats. If you have office 2007 and above, you will have the ACE provider and it works for both new and old (2003+) access databases. Follow the same approach you used in your original code to create a connection object and open it, then set the active connection of your command object to the newly opened connection object.

Also, I think I skipped the ONE table stuff here (and even the loop). But to use ONE table, add another field called "Record_Type" and it will hold the values for "AllSamples", "Genetics", "Reprep", "RapidFire", "Quest_SendOut" and so on .... Then when you write your query, instead of picking a "table" you just use a "record_type" as a criteria in your query. Since this field is used in search criteria, it should also be indexed, if you have many thousands of records. Indexes aren't important in very small tables.

For that matter, if we are going to go ONE table, why not ONE database? Add another field called date_created. Put a timestamp in the table. Now you can dump your data into the same database all the time too ...

And again, I think creating tables in code is an academic exercise. You could simply have a template table or a table that your first clear all the existing data from and then repopulate - I very rarely actually use code to create tables, though there is an element of tidiness in not having the template tables in your database if you do use code.

Function CreateTableADO()
    'Purpose:   Create two tables, their indexes and relation using DDL.
    'Requires references to ADO library and ADOX library
    Dim cmd As New ADODB.Command
    Dim s As String
    Dim cat As New ADOX.Catalog
    Dim tbl As ADOX.Table
    Dim ind As ADOX.Index
    Dim arr
    Dim i As Long
    Dim table_name As String
    
    'Initialize
    cmd.ActiveConnection = CurrentProject.Connection
    
    'New Table Name
    table_name = "NEW_TABLE"
    
    s = "CREATE TABLE " & table_name & " (ID COUNTER CONSTRAINT PrimaryKey PRIMARY KEY,"
    s = s & " MR_Num TEXT(20) WITH COMP,"
    s = s & " Chart_Number TEXT(12) WITH COMP,"
    s = s & " Last_Name TEXT(50) WITH COMP,"
    s = s & " First_Name TEXT(50) WITH COMP,"
    s = s & " Date_Received DATE,"
    s = s & " Sales_Rep TEXT(10) WITH COMP,"
    s = s & " Hold_Reason TEXT(255) WITH COMP,"
    s = s & " Pending_Days LONG,"
    s = s & " Notes TEXT(255) WITH COMP)"
    
    
    cmd.CommandText = s
    cmd.Execute
    Debug.Print "table created."
    
    'Indexes (not really that important if table has less than say about 10,000 rows - otherwise use indexes appropriately to improve query performance)
    Set cat.ActiveConnection = cmd.ActiveConnection
    Set tbl = cat.Tables(table_name)
        
    arr = Array("MR_Num", "Chart_Number", "Last_Name", "First_Name", "Date_Received", "Sales_Rep", "Hold_Reason", "Pending_Days")
    For i = 0 To UBound(arr)
        Set ind = New ADOX.Index
        ind.Name = arr(i)
        ind.Columns.Append arr(i)
        tbl.Indexes.Append ind
        Set ind = Nothing
    Next i
    
    Debug.Print "indexes created."
    
    
End Function

 

[mrmmickle1]

xenou,

I had a chance to look at your solution yesterday, but have been having a little trouble getting it to work properly. I want to fool around with it a little more before I report back with more details, but at the current time I am experiencing some strange behavior when I run the code in an open Access Database. The code runs all the way through without any hiccups. The issue is that after it has run I look in the Navigation Pane on the left and the Table that the code added is not visible.... I thought this was odd so I stepped through the code again. When I got to the line:

cmd.execute

I received an error that said the Table already exists.... but like I said it is not visible. I will continue to step through this code and try to open a different connection to an existing database and see what happens with this method. I will keep you posted.

Once again. Thank you for taking the time to look at this issue.

 

[Norie]

Have you tried refreshing the database window/tables tab?

 

[xenou]

I received an error that said the Table already exists.... but like I said it is not visible. I will continue to step through this code and try to open a different connection to an existing database and see what happens with this method. I will keep you posted.

This is just how it is ... you can either wait a minute or two (the table will eventually appear), or you can close and re-open the database (the table will be there then). It just doesn't show up right away. Since you can't create a second table with the same name, you will always get the error if you try to run the code again, because even though you can't see it the table is really part of the database right away.

 

[mrmmickle1]

xenou and Norie,

Thanks so much for your replies. The table did appear as you both mentioned. MS Access must have just needed a minute to process the request. I really appreciate all of the help. I am trying to pick up some MS Access VBA skills but it seems things are progressing a little slow for me in this subject matter ahahah. I really appreciate you all helping to extend my knowledge in this area.