excel add-in with data from access

[pygmalion]

Hi

I'm posting this here, because i'm not sure where else and i have a gut feeling i will be doing this with VB.

My goal is the following:
I have an Access database (with four fields, of which the first one is the key) which I'd like to use in Excel.
The table contains about 1,5 million records, and the idea is the user in Excel should enter a number (which should represent the first field in the table), and by the click of a button, excel should pick up the three other fields of the record.
Now, I have searched a lot, and am not sure what's the best way to approach such a challenge.

I'd much appreciate any advice on the matter, as this project is of quite an importance to me.

Thanks very much in advance
pygmalion

[timothybard]

Here is a link to a file that shows how to connect to an Access database from Excel: http://www.oecd.org/dataoecd/55/15/35199750.pdf

It is dated and uses an older version of office, but the procedure is the same. While building the query, you are able to select the records based on criteria, such as the key you mention in your post. When you create the query, just select a record to import. Once you have the data imported, turn on the macro recorder in Excel and change the criteria so a different record is selected. Turn off the macro recorder and then you will have the VBA code to select individual records. Modify the VBA to your needs to allow the users to select their own records.

[pygmalion]

Thank you very much for your answer. I did what you said and it worked fine. Now, when I try to tweak the code somewhat to get what I ultimately want, I start getting nasty errors.

This is the code I currently have.

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 24/04/2009 by User
'
    Dim quote As Variant
    quote = Range("A8").Text
    
       With ActiveSheet.QueryTables.Add(Connection:=Array( _
        "ODBC;DSN=MS Access Database;DBQ=C:\db1.mdb;DefaultDir=C:\;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"), Destination:=Range("A9"))
        .CommandText = Array("SELECT `CATERPILLAR-PRICES-2009 Query`.Field1, `CATERPILLAR-PRICES-2009 Query`.Field2, `CATERPILLAR-PRICES-2009 Query`.Field3, `CATERPILLAR-PRICES-2009 Query`.Field4" & Chr(13) & "" & Chr(10) & "FROM `CATERPILLAR-PRICES-2009 Query` `CATERPILLAR-PRICES-2009 Query`" & Chr(13) & "" & Chr(10) & "WHERE (`CATERPILLAR-PRICES-2009 Query`.Field1= & quote &)")
        .Name = "Query from CatDB"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = True
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
    End With
    
    Range("E6").Select
End Sub

When executing this code, I get the following error (which seems to originate from the "ODBC;DSN=MS..."-line).Run-time error '13':
Type mismatch

What i'd like to achieve more is:

1) Arrange the results to come to different cells (not just one next to the other), plus without the column titles.
2) Excel should execute this on more than one entry (entries are listed in same column one below the other).
3) How to execute that macro through a toolbar button [although i'm sure to find this online explained somewhere]

Any help with this is very much appreciated. I've been working on this problem for over a week (tried many things, including straight from Visual Studio in C#, but to no avail), and for the first time I'm starting to see progress (thanks totimothybard!).

Thanks again in advance
pygmalion

[timothybard]

As far as your error.. can you show the original code, which I assumed worked, and the modified code so that we can see the changes you made.

On the line you referenced, you have the following:

FROM `CATERPILLAR-PRICES-2009 Query` `CATERPILLAR-PRICES-2009 Query`

Why is the query mentioned twice?

As far as your other concerns:
1) If this works, I would have the results in a separate sheet and then pull over the information that you want either through vba code, reference or lookup.
2) I would use VBA to loop through each entry and update the single query and then have code to retrieve the required results and place them where you want them
3) Executing macros through toolbars is rather straightforward once you find a resource showing how. Let me know if you are unable to find a resource or need specific help

Tim

[pygmalion]

Thanks for your answer. Here's the original code (created automatically, as you had suggested), which I then intermingled with other code I found in online examples. Maybe I should go back and depart only from this code?

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 27/04/2009 by User
'

'
    With Selection.QueryTable
        .Connection = Array(Array( _
        "ODBC;DSN=MS Access Database;DBQ=C:\db1.mdb;DefaultDir=C:\;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" _
        ))
        .CommandText = Array( _
        "SELECT `CATERPILLAR-PRICES-2009 Query`.Field1, `CATERPILLAR-PRICES-2009 Query`.Field2, `CATERPILLAR-PRICES-2009 Query`.Field3, `CATERPILLAR-PRICES-2009 Query`.Field4" & Chr(13) & "" & Chr(10) & "FROM `CATERPILLAR-PRICES-2009 Que" _
        , _
        "ry` `CATERPILLAR-PRICES-2009 Query`" & Chr(13) & "" & Chr(10) & "WHERE (`CATERPILLAR-PRICES-2009 Query`.Field1='5N8380')" _
        )
        .Refresh BackgroundQuery:=False
    End With
End Sub

About the other subjects:

1) I see. And how do I specify in the VBA code (above) the target sheet for the retrieving of data?
2) This sounds excellent, but you could you please go a little more in details, in how I would practically go about and do that. (My syntaxic knowledge in VBA is quite limited).
3) Thanks. I hope I can find some recource on my own.

Thanks again very much for all your support. Seriously, very much appreciated.

Regards
pygmalion

[timothybard]

Just so I'm clear, the code in the previous post does work, but you want to be able to select which number is selected (instead of always just showing number 5N8380). Is that correct?

[timothybard]

If the code in the previous post did work, then I would try the following code:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 27/04/2009 by User
'

'
	Dim quote As String
    quote = Range("A8").Text

    With Selection.QueryTable
        .Connection = Array(Array( _
        "ODBC;DSN=MS Access Database;DBQ=C:\db1.mdb;DefaultDir=C:\;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" _
        ))
        .CommandText = Array( _
        "SELECT `CATERPILLAR-PRICES-2009 Query`.Field1, `CATERPILLAR-PRICES-2009 Query`.Field2, `CATERPILLAR-PRICES-2009 Query`.Field3, `CATERPILLAR-PRICES-2009 Query`.Field4" & Chr(13) & "" & Chr(10) & "FROM `CATERPILLAR-PRICES-2009 Que" _
        , _
        "ry` `CATERPILLAR-PRICES-2009 Query`" & Chr(13) & "" & Chr(10) & "WHERE (`CATERPILLAR-PRICES-2009 Query`.Field1='" & quote & "')" _
        )
        .Refresh BackgroundQuery:=False
    End With
End Sub

Please note that I added the dim quote as string line, the quote = Range("A8") line and I changed Field1='5N8380')" to .Field1='" & quote & "')"

If the original code worked, then this should allow the user to enter something into cell A8 and then update the data based on that cell after the code is run.

[pygmalion]

Thanks very much, it works like a charm.

Finally, I'm having difficulty with the option of being able to apply the query to many entries at the same time (which are just entered one under the other). I tried quite a few things (from just extra variables and changing the select-statement to include those, to loops) but it just doesn't seem to be working. I always somehow end up with the error statement:

Run-time error '1004'

Anyway, hope you can help me out with this last thing.
Thanks again very much for your help.

[timothybard]

Are you trying to allow the user to type in several quote values, all right under each other, and have the query show the data for each of those quote values?

You can try something similar to this:

'This code takes a range and puts the values into a common delimited string
    Dim cell As Range
    Dim values As String
    
    For Each cell In Range("A8:A10")
        values = values & "'" & cell & "', "
    Next
    
    values = Left(values, Len(values) - 2)
    
    'This code updates the query with the values
	With Selection.QueryTable
        .Connection = Array(Array( _
        "ODBC;DSN=MS Access Database;DBQ=C:\db1.mdb;DefaultDir=C:\;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" _
        ))
        .CommandText = Array( _
        "SELECT `CATERPILLAR-PRICES-2009 Query`.Field1, `CATERPILLAR-PRICES-2009 Query`.Field2, `CATERPILLAR-PRICES-2009 Query`.Field3, `CATERPILLAR-PRICES-2009 Query`.Field4" & Chr(13) & "" & Chr(10) & "FROM `CATERPILLAR-PRICES-2009 Que" _
        , _
        "ry` `CATERPILLAR-PRICES-2009 Query`" & Chr(13) & "" & Chr(10) & "WHERE (`CATERPILLAR-PRICES-2009 Query`.Field1 IN (" & values & "))" _
        )
        .Refresh BackgroundQuery:=False
    End With

The first part of the code will take a range of cells and produce a string that stores all the values in one place. When you run the code, the values variable should store something that looks like this:

'Quote1', 'Quote2', 'Quote3'

Of course, instead of Quote1, it will be the first value the user typed in.

The second part of the code is exactly the same as the code I previously posted with the exception of the WHERE clause. In my original code, I used a where clause of Field1 = 'Quote'... In the code I just posted, the WHERE clause will look like this:
Field1 IN ('Quote1', 'Quote2', 'Quote3')
This will return all the records where Field1 is one of the three values.

I don't know how many values the users will enter, so you may need to change the range. If you use named ranges, then you can simply put the name in the Range function and just update the named ranges to include additional values.

Let me know how it works or if you run into any issues.

[pygmalion]

Thank you so much for the code, it works amazingly well. The way I arranged it is that user enters everything in a 3rd sheet, while the data is formatted as should be in the other two sheets (referenced twice the same for different languages).

One problem that does still seem to occur, is that I can only give five entries to generate (while I have entered a range of almost 35 entries - which I would like to put till at least 250). Any more than that, and Excel gives a Run-time error ('13' or '1004' - "Type mismatch"). It may be a bit hard to explain, so I uploaded the file to my server. Maybe you can have a look at it, and see what causes the trouble.
Also, if a different cell (than one of the entered entries) is selected while executing the Macro, a same error is given.

Thank you so much
pygmalion

[After some consideration, I decided to send it to you via private message - if anyone else is willing to help, I'd gladly send it to him/her as well]

[timothybard]

You are getting error 1004 because of the line:

With Selection.QueryTable

Since you have this line, you need to make sure that the selected cell when you run the code is part of the query table. However, you can avoid this by replacing the line

With Selection.QueryTable

with

With Range("B2").QueryTable

Error 13 takes a little more explanation. MS Excel uses SQL to connect to data sources. SQL stands for Structured Query Language and is a common language used to connect to data sources by all types of programs. When you use SQL in Excel, you are limited to, I believe, 255 characters in your SQL statement. The SQL statement within your code is below:

SELECT `CATERPILLAR-PRICES-2009 Query`.Field1, `CATERPILLAR-PRICES-2009 Query`.Field2, `CATERPILLAR-PRICES-2009 Query`.Field3, `CATERPILLAR-PRICES-2009 Query`.Field4 FROM `CATERPILLAR-PRICES-2009 Query` `CATERPILLAR-PRICES-2009 Query` WHERE (`CATERPILLAR-PRICES-2009 Query`.Field1 IN (" & values & "))

Since you are using a large range of values (A2 through A35), you are running into the character limit.

This issue isn't quite as straight-forward to solve as the previous issue. I would recommend creating a loop that loops through cells A2 through A35. During each iteration of the loop, grab the value of the current cell, update the SQL statement so it uses the value of that cell, copy the results of the SQL Statement, which would be located in columns B, C and D, and paste them into different columns, say J,K and L, and in the same row as the current cell of the range you are iterating through.