Visual Basic Between Excel and Access

I'm hoping that someone can help me on this problem. Here's what I have to do.

I have a large access database with approximately 1 Million rows. I need to have a user select certain criteria in an Excel workbook, then depending on that selection, choose a certain table from an Access database. Or, even better, take their selection, intellegently update the access query, then export the data back to Excel. I think I've figured out how to use the TransferSpreadsheet method from Access, however, how do I go about getting variables defined in Excel to be read in access. Is that even possible?

Here's an example

In Excel:

Select a state, for example: State = Michigan

In Acess

Look up a certain table, run a statement that says, "only give me michagan results" and then export that Michigan data back to Excel.

Can anyone help? I want to make this automated as possible.

Hi Tuba,

You have 2 option for doing this.

One option is to use an 'external data query' (see selections in Excel under Data >> Import External Data), which will run either an OLEDB or ODBC query.

The other option is to use 'Automation'; this is a "pure VBA" solution in which you would basically be "controlling" Access using VBA in Excel.

Most people use the first option. There are advantages and disadvantages to both. If getting the data into Excel is only the first step, and you will also have a bunch of manipulations in Excel, then the 'Automation' method is probably better for you because it provides complete control over the entire process.

On the other hand, if you are not too confident in your VBA ability, then the 'external data query' option has the advantage that you can get almost to the final solution simply by recording a macro.

For either method to work, you must have a minimal level of competence in SQL.

Originally Posted by MSP77079

Hi Tuba,

You have 2 option for doing this.

One option is to use an 'external data query' (see selections in Excel under Data >> Import External Data), which will run either an OLEDB or ODBC query.

The other option is to use 'Automation'; this is a "pure VBA" solution in which you would basically be "controlling" Access using VBA in Excel.

Most people use the first option. There are advantages and disadvantages to both. If getting the data into Excel is only the first step, and you will also have a bunch of manipulations in Excel, then the 'Automation' method is probably better for you because it provides complete control over the entire process.

On the other hand, if you are not too confident in your VBA ability, then the 'external data query' option has the advantage that you can get almost to the final solution simply by recording a macro.

For either method to work, you must have a minimal level of competence in SQL.

So working with your suggestions and with some of the links you've provided, I was able to make a little bit of progress. I was able to record the macro using the "Import External Data" as you suggested, and I think that this will probably work out for what I want to do. However, I'm trying to clean up the code, using something you suggested in one of the other posts, and now I'm getting an error message. here is my code.

Dim FirstArray(4) As String

FirstArray(1) = """ODBC;DSN=MS Access Database;"
FirstArray(2) = "DBQ=C:\My Data\Reserve Analysis Redesign\closure.mdb;"
FirstArray(3) = "DefaultDir=C:\My Data\Reserve Analysis Redesign;"
FirstArray(4) = "DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"""

With ActiveSheet.QueryTables.Add(Connection:=Array(FirstArray), Destination:=Range("A1"))

       .CommandText = Array("SELECT `Claim Closure Information`.Claim_Number" & Chr(13) & "" & Chr(10) & "FROM `C:\My Data\Reserve Analysis Redesign\closure`.`Claim Closure Information` `Claim Closure Information`" & Chr(13) & "" & Chr(10) & "WHERE (`Claim Closure Information`.Claim_N" _
        , "umber='602-161388')")
                .Name = "Query from MS Access Database"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

Now, Everything is the same as was recorded except for the begining, where I'm trying to use that "FirstArray" string. I'm just trying to clean up that begining part. However, when I do this, I get an error reading "Incomplete Datasource" when the code comes to ".Refresh Background Query:=false".

Any idea? Am i just missing something? Oh yea, here is the code that I recorded that actually works.

With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
        "ODBC;DSN=MS Access Database;DBQ=C:\My Data\Reserve Analysis Redesign\closure.mdb;DefaultDir=C:\My Data\Reserve Analysis Redesign;Dri" _
        ), Array("verId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;")), _
        Destination:=Range("A1"))
        .CommandText = Array( _
        "SELECT `Claim Closure Information`.Claim_Number" & Chr(13) & "" & Chr(10) & "FROM `C:\My Data\Reserve Analysis Redesign\closure`.`Claim Closure Information` `Claim Closure Information`" & Chr(13) & "" & Chr(10) & "WHERE (`Claim Closure Information`.Claim_N" _
        , "umber='602-161388')")
        .Name = "Query from MS Access Database"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

Any help is much appreciated.

Glad you posted the code that does work.

First of all, the fact that the error message comes only when you get to the ".Refresh" command doesn't tell us all that much. What it tells us is that VBA is happy with the code, but ODBC is not happy with it at all.

The fact that the error message says "Incomplete Datasource", means that (as you probably guessed already) what ODBC does not like is the Connection String.

I notice a few things:

+ I would remove "" from FirstArray(1) = """ODBC;DSN=MS Access Database;"
In other words, just make it FirstArray(1) = "ODBC;DSN=MS Access Database;"

Similary, make FirstArray(4) = "DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"

The extra quotes are unquestionably a problem.

If making this change it works, then ignore the next item.

In addition:
+ like all others I have seen, your connection array has an array that contains an array
+ unlike any I have ever seen, the first of these arrays is also an array; I wonder why? And, I wonder if it is necessary to maintain it that way?

Having said all of that ... I think what you need to work on is not the Connection String. I was suggesting that for someone else because the name of their file would change from one run to the next, and getting a handle on their connection string was important for them. As long as the name & location of your Access database is not going to change, you can use the Connection string that the macro recorder gave you.

What you need to work on is the SQL string.

.CommandText = Array("SELECT `Claim Closure Information`.Claim_Number" & Chr(13) & "" & Chr(10) & "FROM `C:\My Data\Reserve Analysis Redesign\closure`.`Claim Closure Information` `Claim Closure Information`" & Chr(13) & "" & Chr(10) & "WHERE (`Claim Closure Information`.Claim_N" _
, "umber='602-161388')")

This text is what actually runs the query on the Access database. This is the string we need to change in order to allow the user to make their selections in Excel, and then pass those selections to Access (as a revised "WHERE" statement).

Originally Posted by MSP77079

Glad you posted the code that does work.

First of all, the fact that the error message comes only when you get to the ".Refresh" command doesn't tell us all that much. What it tells us is that VBA is happy with the code, but ODBC is not happy with it at all.

The fact that the error message says "Incomplete Datasource", means that (as you probably guessed already) what ODBC does not like is the Connection String.

I notice a few things:

+ I would remove "" from FirstArray(1) = """ODBC;DSN=MS Access Database;"
In other words, just make it FirstArray(1) = "ODBC;DSN=MS Access Database;"

Similary, make FirstArray(4) = "DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"

The extra quotes are unquestionably a problem.

If making this change it works, then ignore the next item.

In addition:
+ like all others I have seen, your connection array has an array that contains an array
+ unlike any I have ever seen, the first of these arrays is also an array; I wonder why? And, I wonder if it is necessary to maintain it that way?

Having said all of that ... I think what you need to work on is not the Connection String. I was suggesting that for someone else because the name of their file would change from one run to the next, and getting a handle on their connection string was important for them. As long as the name & location of your Access database is not going to change, you can use the Connection string that the macro recorder gave you.

What you need to work on is the SQL string.

.CommandText = Array("SELECT `Claim Closure Information`.Claim_Number" & Chr(13) & "" & Chr(10) & "FROM `C:\My Data\Reserve Analysis Redesign\closure`.`Claim Closure Information` `Claim Closure Information`" & Chr(13) & "" & Chr(10) & "WHERE (`Claim Closure Information`.Claim_N" _
, "umber='602-161388')")

This text is what actually runs the query on the Access database. This is the string we need to change in order to allow the user to make their selections in Excel, and then pass those selections to Access (as a revised "WHERE" statement).

First, i want to thank you for your attentiveness to this. I'm very grateful to you and the fact that we have a forum like this. I don't have much experience here, but every question I have asked has always been answered so quickly. Thank you.

Second, I have no idea why I thought I needed that double quote in there. I guess I thought that since the recorded macro had a quote, I needed to reproduce the quote, which makes absolutely no sense. I now think that the recorded macro was just designating the string...

So, taking out the double quote fixed everything. The reason I did it this way is becuase I thought it was cleaner. I am planning on implementing something similar for SQL statement in the .CommandText part of the code. One step at a time though.

Thanks for your suggestions about how to do this. I plan on implementing it exactly how you suggested: using an input variable to interact with the "WHERE" part of the SQL. It seems like it should work this way.

Can you confirm one more thing for me. The char(13) and Char(10) part, is that just an ASCII thing designating a carrage return? I have minimum competency in SQL, but from what I've seen does each command have to be on a separate line, thus this is how windows would have to implement it?

Again, thanks for your help.

Yes, the chr(13) & Chr(10) (no "a") is the same thing as vbCr & vbLf; or more simply vbCrLf.

You might have seem my instructions in one of the other threads. In case you missed it ... the easiest way to get the SQL correct is to create the query in Access first; then view the SQL there.

In Access you will see that each part of the SQL string starts on a new line. By this I mean the "SELECT" is alway first, the "FROM" starts on a new line, the "WHERE" starts on a new line.

This is NOT necessary. As long as you leave one SPACE (no comma!) between the "SELECT" and the "FROM", it will work just fine without any vbCrLf. But, if you like them there, by all means, go for it. It does make it easier to read in Access. But, you will be busting it apart in Excel anyway, so the vbCrLf is not all that helpful for the ODBC code.

My firm advice is to test the SQL string in Access first, and then to move it to Excel.

I'm slightly familiar with Excel/Access stuff, but only slightly. (So my response is by no means close to complete)

If you're predominantly dealing with Excel, could you not do all your work from Excel, and just pull the data from your Access database, instead of doing it through Access and pulling the other way?

I know you can formulate your query in Excel using macros and/or a Database Query. Of the two ways, at least for me, dealing with it all through Excel is easier than dealing with it all through Access -- mostly a familiarity thing though.

This is one of those things I've been meaning to brush up on a bit, but haven't got around to.

Scott

Here are links to a couple of recent postings dealing with this same subject, they might give you more ideas about how to get started.

http://www.excelforum.com/showthread.php?t=583277


http://www.excelforum.com/showthread.php?t=583785

Hm... let me work on this a little bit and get bakc to you. Thanks for the quick responses.