Using Access Query w/Variable

[cstimart]

I have an access 2007 database that has a handful of queries that are date range driven. Currently, I have to open the db and put the dates in a form...then the queries provide me the data for that data range.

Is it possible to use excel to connect to the access db and set those dates via a cell or variable in excel...therefore filling different tabs with the query data?

 

[xenou]

Hmmm...that is intriguing. Is it easily converted to create/utilize an Access 2007 database? I tried changing the Constant to '.accdb' but that crashes the code later on.

if you are connecting to an Access 2007 database with ADO you have to use the ACE engine, not the JET engine:

http://www.connectionstrings.com/access-2007

 

[SydneyGeek]

Yes, the connection string is the issue.

Where you create the connection, use this instead:

    With cnn
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .Open MyConn
    End With

Denis

 

[cstimart]

When the code now creates the Access 2007 database, is it possible for it to change the setting(s) to have the tabbed documents (not overlapping) and "Compact on Close"?

 

[SydneyGeek]

Undoubtedly, but I have never bothered. I set that up manually because it's once only for the life of the database.

Denis

 

[cstimart]

OK...so I'm diving in. Here's my code...

Const TARGET_DB = "Data.accdb"
Sub CreateDB_And_Table()
  
    Dim cat As ADOX.Catalog
    Dim tbl As ADOX.Table
    Dim sDB_Path As String
    
    sDB_Path = ActiveWorkbook.Path & Application.PathSeparator & TARGET_DB
    
    'delete the DB if it already exists
    On Error Resume Next
    Kill sDB_Path
    On Error GoTo 0
    
    'create the new database
    Set cat = New ADOX.Catalog
    cat.Create _
        "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & sDB_Path & ";"
    
    'create the table
    Set tbl = New ADOX.Table
    tbl.Name = "tblData"
    tbl.Columns.Append "Column_01", adDouble
    tbl.Columns.Append "Column_02", adVarWChar, 60
    tbl.Columns.Append "Column_03", adVarChar, 60
    tbl.Columns.Append "Column_04", adVarWChar, 25
    tbl.Columns.Append "Column_05", adDate
    tbl.Columns.Append "Column_06", adDate
    tbl.Columns.Append "Column_07", adDate
    tbl.Columns.Append "Column_08", adWChar, 9
    tbl.Columns.Append "Column_09", adCurrency
    tbl.Columns.Append "Column_10", adDate
    tbl.Columns.Append "Column_11", adDate
    tbl.Columns.Append "Column_12", adDate
    tbl.Columns.Append "Column_13", adCurrency
    tbl.Columns.Append "Column_14", adInteger
    tbl.Columns.Append "Column_15", adCurrency
    cat.Tables.Append tbl
    
    Set cat = Nothing
    
    'now create the primary key
    Call CreatePrimaryKey("tblData", "Column_01")
End Sub

The code bombs on "cat.Tables.Append tbl" with a run-time error saying "Type is invalid."

For my references, I have...

1. Visual Basic For Applications
2. Microsoft Excel 12.0 Object Library
3. OLE Automation
4. Microsoft Office 12.0 Object Library
5. Microsoft ActiveX Data Objects 2.8 Library
6. Microsoft ADO Ext. 2.8 for DDL and Security.

Any ideas/changes?

 

[xenou]

    tbl.Columns.Append "Column_03", adVarChar, 60

Amend to:

    tbl.Columns.Append "Column_03", [COLOR="Red"]adVarWChar[/COLOR], 60

See:
http://support.microsoft.com/kb/266302

I found this by just typing in the error number in google.

 

[cstimart]

I'm assuming that I can skip the Primary key portion, as my data could have duplicate values in Column 1.

Also, this section of code crashes when the cell is empty. How can I get this to leave the Access field as Null?

    For i = 2 To Rw
        rst.AddNew
        For j = 1 To 15
            rst(Cells(1, j).Value) = Cells(i, j).Value
        Next j
        rst.Update
    Next i

 

[xenou]

Are you working on SydneyGeek's tutorial or your original database? You are losing me going back and forth between these things.

I'd recommend go through the tutorial first. Learn how it works. Then apply the lesson to your database.

Re, the empty cells:
For i = 2 To Rw
rst.AddNew
For j = 1 To 15
If Len(Cells(1,J).value > 0 then
rst(Cells(1, j).Value) = Cells(i, j).Value
End If
Next j
rst.Update
Next i

 

[cstimart]

Are you working on SydneyGeek's tutorial or your original database? You are losing me going back and forth between these things.

I'd recommend go through the tutorial first. Learn how it works. Then apply the lesson to your database.

Re, the empty cells:
For i = 2 To Rw
rst.AddNew
For j = 1 To 15
If Len(Cells(1,J).value > 0 then
rst(Cells(1, j).Value) = Cells(i, j).Value
End If
Next j
rst.Update
Next i

Sorry, I'm using the tutorial and using the code to populate access with my data.

 

[xenou]

Maybe just follow the tutorial as is? It comes with a sample file of data so you shouldn't have any problems. Just follow along and learn how it works... then come back to your database to apply the lessons learned. Easier.