SubscribeUsing Excel As Your Database
This is a guest post by Vijay, our in-house VBA Expert.
Often I have thought, if I could have write “Select EmployeeName From Sheet Where EmployeeID=123″ and use this on my excel sheet, my life would be simpler. So today we will learn how to do this.
People spend a lot of time thinking whether to use Excel as their database or not. Eventually they start using Access or SQL Server etc.
Today we will learn how to use Excel as a Database and how to use SQL statements to get what we want.
Excel as a Database – Demo
We will learn how to build this:
Before we begin:
- The entire sheet (where the raw data has been stored) will be referred as one single database table by Excel. This is very important to understand.
- This has nothing related with the in-built Table (2007 and greater) / List (2003 and previous) feature of Excel.
- If you know SQL (Structured Query Language) your task becomes much easier.
Setting up Excel as Database
We need some raw data and we will utilize Customer Service Dashboard sample database here.
Let’s get started.
First we will design the structure of what all option we want to present for filtering the data, which you can see in the interface below.
Once the user clicks on Show Data we will use a SQL statement to filter-out the data as per the drop down options selected by the user and the put them in the table below.
We will also use another SQL statement to populate the top right hand side table for calls data when all the 3 drop downs have some options selected.
Adding Active-x data objects reference
We need to add a reference to the Microsoft ActiveX Data Objects Library to be able to use the worksheet as a database table. You can do this from Visual Basic Editor > Tools.
I usually select the most recent version, however if you are developing a product it will be best suited if you are familiar with the operating system and office version used by the end-user’s system and accordingly select the best version available.
Opening Excel as a Database
Once this is done we need to hit the road with some VBA code.
Public Sub OpenDB()
If cnn.State = adStateOpen Then cnn.Close
cnn.ConnectionString = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" & _
ActiveWorkbook.Path & Application.PathSeparator & ActiveWorkbook.Name
cnn.Open
End Sub
The above procedure is the heart of this post, this is where we define how to use the current Excel workbook as our database.
cnn.ConnectionString = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" & _
ActiveWorkbook.Path & Application.PathSeparator & ActiveWorkbook.Name
On this line, we define all the possible file extensions that we are allowed to create an Excel Workbook and then use as our database.
Let’s understand the code module
When you click on the Update Drop Downs button, the VBA code uses the “Data” worksheet as a table and then finds unique values for Products, Region and Customer Types and then populates them as ListItems for the ComboBox controls.
Example for Products drop down
strSQL = "Select Distinct [Product] From [data$] Order by [Product]"
closeRS
OpenDB
cmbProducts.Clear
rs.Open strSQL, cnn, adOpenKeyset, adLockOptimistic
If rs.RecordCount > 0 Then
Do While Not rs.EOF
cmbProducts.AddItem rs.Fields(0)
rs.MoveNext
Loop
Else
MsgBox "I was not able to find any unique Products.", vbCritical + vbOKOnly
Exit Sub
End If
What is important to notice here is how the Table and Fields have been identified using square brackets unlike traditional SQL where we just provide the name, also the table name has to be suffixed with a $ symbol at the end.
As I have suggested earlier, one entire sheet will be treated as one single table, so if you have multiple datasets that were currently organized within one sheet you may have to create multiple sheets to store that data to be able to use them as tables. This would also make maintenance of data easier.
Using Excel SQL to consolidate two sheets in to one
Many people ask, how to consolidate 2 or more sheets which have the similar data. Well I would have adopted this method and wrote a simple query as below.
SELECT ID, FirstName, MiddleName, LastName, Age, DOB From [Table1$]
UNION ALL
SELECT ID, FirstName, MiddleName, LastName, Age, DOB From [Table2$]
This would allow me to use both the sheets as one table and fetch all of my data into a new sheet.
Download Excel As Database Demo File
Click here to download the demo file & use it to understand this technique.
Do you use Excel as a database?
Do you also user Excel as your database? If yes please put in the comment below how do you use the same and what has been your experience. Leave a comment.
More on VBA & Macros
If you are new to VBA, Excel macros, go thru these links to learn more.
Join our VBA Classes
If you want to learn how to develop applications like these and more, please consider joining our VBA Classes. It is a step-by-step program designed to teach you all concepts of VBA so that you can automate & simplify your work.
Click here to learn more about VBA Classes & join us.
About Vijay
Vijay (many of you know him from VBA Classes), joined chandoo.org full-time this February. He will be writing more often on using VBA, data analysis on our blog. Also, Vijay will be helping us with consulting & training programs. You can email Vijay at sharma.vijay1 @ gmail.com. If you like this post, say thanks to Vijay.
| ||||
|
| ||||
|
Leave a Reply
 |
Comprehensive Guide to VLOOKUP & Other Lookup Formulas | Sign-up for my Excel Dashboard Masterclass in Australia |  |
At Chandoo.org, I have one goal, "to make you awesome in excel and charting". This blog is started in 2007 and today has 450+ articles and tutorials on using excel, making better charts. 
96 Responses to “Using Excel As Your Database”
Worst thing ever to do. Excel is as much a db as is Access.
Use excel as a spreadsheet and leave databases work to databases.
It’s bad enough seeing spreadsheets with multiple worksheets being used and screwed up by people without letting them think it’s a database.
Excel has a place in business just not as a db.
In many companies DB people are usually overworked and everyone else is just stuck with large Excel data files that need to be manipulated. In such cases it’s unreasonable to expect people to wait until the DB person gets to their issue so managing the data in Excel is a good option.
Worst thing ever to do. Excel is as much a db as is Access.
Use excel as a spreadsheet and leave databases work to databases.
It’s bad enough seeing spreadsheets with multiple worksheets being used and screwed up by people without letting them think it’s a database.
Excel has a place in business just not as a db.>>>
I might have agreed with you thru Excel 2007 but since the advent of PowerPivot, Excel is as much a database as any other relational database. I suggest you read either Rob Collie’s book “DAX Formulas for PowerPivot or Bill Jelen’s Power Pivot for the DATA Analyst” (emphasis mine) This is a gamechanger
I don’t like negative comments like that one, I must say!
If you like argue about the sex of angels, I would say that Access is not a bullet proof database either…
This is a great tutorial and it helps me a lot. I have two tables in Access, one with the provinces, districts and postal codes in Thailand. I added the second one with the tambon (subdistricts). The first has got 928 records, the second 7373 records. I want to keep only one table by writing the postal codes in the second table. This is a one shot action, so why should I modify my VB6/ADODB/SQL/MsJet programme for that purpose??? I am glad to use SQL instead of a ‘Find’ in VBA.
Thanks a lot Chandoo
Hey Chandoo is this taught at Excel School? Man, this is the awesomeness thing I had ever seen!
@Kafran… This is included in our VBA class. Visit http://chandoo.org/wp/vba-classes/ if you would like to join us.
Excellent and thanks Vijay for providing such as an easy way to create DB using EXcel.
Ashwin
Excel has database functions. http://office.microsoft.com/en-us/excel-help/CH006252820.aspx
If you use this, you can avoid VBA. I use these for work and visualization purposes. A sample file can be downloaded here http://pankaj.dishapankaj.com/share
Fantastic.
I use Excel as a ‘database’ but using more conventional (non-vba) methods so this is great.
I agree with Graham, in so far as Excel is not designed to be a database and therefore you should keep its use to a minimum and make excel do what its best at, number and data manipulation, not data relationship management. However, on occasion it is very handy to use it in a minor way as a database instead of having to link different applications (which sometimes is not possible due to IT security policies) to achieve a result.
More articles like this are a must.
Thanks
Dave
Great! What a coincidence? Only yesterday, I was searching for “database” in this site & did not get much information.
While I agree that Access is better used as a database, there are situations where we are forced to use Excel. Please write some more articles on databases. Thanks Vijay!
Thank you very much Vijay this is really creative work. I hope it will continue.
I have liked your teaching on vba class too. thanks again.
I agree about the fact that Excel isn’t a database but I think it’s great. See practical use in a select numbers of situations. Use it wise I would like to say. Thanks for the information and I looking forward on more topics.
Regards
Gerard
Good little demo. Note that the square brackets are only needed if your table or column name has a space in it. But I think its good practice to use them whether or not your table or column name has a space in it.
You can also use this character to do the same: `
For instance, you could remove the brackets from [data$] with and the query still works fine. But you could not remove the brackets from [Customer Type] because of the space, although you could use `Customer Type` in place of [Customer Type].
In regards of whether you should use Excel as a database, I don’t see why not. What’s worth pointing out is that you can also modify this approach to take data from just about any database and present it in Excel.
I’d be inclined to not hardcode the sheetname into this code. Instead i’d add this to both modules:
Dim sSheetName As String
sSheetName = “[" & Sheet2.Name & "$]”
…and then replace all 11 instances of this:
[Data$]
…with this:
” & sSheetName & ”
(including the quote marks)
I have been using EXCEL as a front-end for managing data in Access DB. But could not find any easy way to manage the movement-between-records and other features that were available with VB6 “Data Control”.
Is there anything equivalent available in EXCEL 2010?
Regards,
@Uday,
The VB6 Data Control was built for such tasks and functionality. However there is nothing native within Excel to support this.
Could you specifiy what exactly you are after and maybe we can create something that would benefit everyone.
Thanks for the response Vijay. I have built a utility in EXCEL/Access for maintaining my Expense/Income as well as Investments in a consolidated form that always gives me up-to-date reports.
The data is obviously in Access and so is the “Master/Transaction Maintenance”. What I have done is built the maintenance function using EXCEL VBA/Forms but you end up writing a huge code for all functions explicitly (add/modify/delete/view).
Wanted to know if there is anything available that can reduce this development time?
Cheers,
Hi Uday. A very clever guy i’ve got to know called Craig Hatmaker has written a great Excel App to update an external database. It’s still a work in progress, but it’s very very clever, and there are some very good functions to allow you to modify the database that you could probably amend.
To quote from Craig’s own documentation:
There are times when Excel is perfect for manipulating data in databases. Examples include tariff/rate tables, journal entries, and employee time sheets. Excel also works well for maintaining simple lists like code files.
Traditional approaches to leveraging Excel in data entry have end users key data into a formatted spreadsheet, save it to a shared directory, then run a server side program to check data, and if everything is okay, update the database. This approach has multiple objects on different platforms involved in a single task complicating development and maintenance. In addition, validation results are delivered well after entries have been made frustrating end users.
A better approach, in my opinion, is to put that same validation and update logic from the server program into the Excel entry sheet so there’s just one object to maintain. This has the additional benefit of providing end users with immediate feedback as to the validity of entries.
Though this App specifically updates the Employee 2003 table in Microsoft Access’ demo database, Northwind 2003.accdb, the template can be easily modified for use with any database.
Craig tells me he’s happy for me to share the file with you. Flick me a line at weir.jeff@gmail.com and I’ll send you a link to the documentation as well as the access table and excel app he uses.
Also, check out Craig’s blog at
http://itknowledgeexchange.techtarget.com/beyond-excel/forward/
Hi Vijay ,
Thanks.
Narayan
I agree with most of the comments. Never use Excel as a DB.
Use Access or another DB to handle all your data then just set up an odbc and use Excel to make pretty the data.
hi,
i hace ado 2.6 . i use windows xp and office 2007.
how can i find version 6.0.
regards
Aditya
@Aditya,
Version 6.0 is available from Windows Vista onwards.
The below link talks about the version history for MDAC.
http://support.microsoft.com/kb/231943
(however there is no mention of 6.0).
Another helpful atricle I found was …
http://www.vbforums.com/showthread.php?t=527673
hi vijay, why when i change the combobox to call the data by date, it’s always say “data type mismatch in criteria expression”. can you help me to solve this problem?
i think its because the date format, maybe
Vijay -
Thank you for giving those of us without a copy of Access the opportunity to keep progressing with Excel. Very good information!
Best-
Susan
Thanks, Vijay for posting on the topic use excel as database. I hope this has helped many of excel users.
Hi Chandoo,
I understand describing this technique as “using Excel as your database”, for a lack of a better term. But it could be misleading. Excel makes for a poor database, but your article is not really bastardising Excel in lieu of a proper database as some may interpret. The main concepts in your article are about using ADO and the Excel ODBC driver. This technique applies whether you’re using a text file, SQL Server tables, or spreadsheets. Therefore, I look at it more like using Excel as your “data source”. I think it would be valuable if you could explain that a little more so that people understand the context. This is a powerful and time saving technique, great for anyone’s arsenal, so it’s sad to see people make blanket statements against it. Although I do agree in principle that databases make better sources the majority of the time, there are valid reasons for using ADO and Excel in this fashion.
In addition, I hope people understand that you can also use query tables and list objects to accomplish the same. The added benefit is that you can record macros to get generic VBA code, don’t have to worry about references, and can also use MS Query to fine-tune your SQL if you’re not fluent.
Lastly, people should understand that there are significant caveats like how data types are determined or issues with complex SQL statements. I can get into my experiences with this if anyone wants more details.
This is great. When I download the Excel database demo I keep getting error. Is the demo not working for others too?
Thanks
It worked for me. I first downloaded it using “open” instead of “save” and I got errors. I looked at the code and the ado connection was referencing the spreadsheet from http://img.chandoo.org/vba… rather than my computer. I then saved the file to my desktop and ran it from there with no problems.
Hi, I just want to add those lines for use in the case your database has one or more field’s date (column), and you need to select data from a interval.
I think this is useful because SQL has many sintaxe’s variation and this works with VBA:
dvenc1 = Format(Sheets(“View”).Range(“G6″).Value, “dd/mm/yyyy”)
dvenc2 = Format(Sheets(“View”).Range(“H6″).Value, “dd/mm/yyyy”)
dpago1 = Format(Sheets(“View”).Range(“G7″).Value, “dd/mm/yyyy”)
dpago2 = Format(Sheets(“View”).Range(“H7″).Value, “dd/mm/yyyy”)
strSQL = “SELECT * FROM [data$] WHERE ([vencimento]” & _
” between #” & dvenc1 & “# And #” & dvenc2 & “#)”
strSQL = “SELECT * FROM [data$] WHERE ([pagamento]” & _
” between #” & dpago1 & “# And #” & dpago2 & “#)”
Hey, I like this. This also has a practical side where the data will “eventually” end up in a true DB.
However, I have a question: I downloaded the sample file, and was unable to run it because the ado library wasn’t activated.
If this file was shared with others, would they also have to enable the component in excel? Is there a way to make this happen automagically(tm)?
Thanks
Jay, I found the code below, that can automatic load/unload the library, if it has been installed in your machine:
‘Put in the module “This workbook”
Private Sub Workbook_Open()
MsgBox “Oi, obrigado por carregar a Biblioteca”, vbInformation, “JFLôbo”
‘Include Reference Library
‘Microsoft ActiveX Data Objects 2.8 Library
On Error Resume Next
ThisWorkbook.VBProject.References.AddFromFile “C:\Program Files\Common Files\System\ADO\msado15.dll”
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
‘remove ADO reference
Dim x As Object
n = Application.VBE.ActiveVBProject.References.Count
Do While Application.VBE.ActiveVBProject.References.Count > 0 And n > 0
On Error Resume Next
Set x = Application.VBE.ActiveVBProject.References.Item(n)
y = x.Name
If y = “ADODB” Then
Application.VBE.ActiveVBProject.References.Remove x
End If
n = n – 1
Loop
End Sub
awesome, thanks.
I also found that I can enable all the “ADO” libraries back to some point, say 2.6, and order their “priority” and save the document. They will show as “missing” if you look in your references, however the code will still run. Seems the original attempted to load 6.0, which I didn’t have. Now, with the others specified, it eventually found one that worked. in other words, the reference was actually saved with the file and automatically loaded, I just didn’t have it.
I’ve never seen a better way to use Excel as a database than via Laurent Longre’s Indirect.ext function. Set up multiple Excel spreadsheets, populate the data, then consolidate them without opening them – I’ve used it many times and it’s fool-proof…no need for VBA, just using your brain for the data analysis piece afterwards.
I tried to modify the query to Select based on [Date Time] and encountered the following error when click on Show Data
[Microsoft][ODBC Excel Driver] Data type mismatch in criteria expression.
This could be due to restriction where the value has to be a text value instead of Date Value.
Any suggestion how to overcome this?
Many thanks to Vijay for this fantastic post
On a post above, dated (04/09), I gave an example of selecting dates into an interval. Dates are on their right format on the cell.
HI Vijay ,
Thank for posting this thread. I used the excel sheet and ammended it according to my data. Now the problem I am facing is that when I run the Update drop down button I am getting error [MICROSOFT][ODBC Excel driver] Too few parameters. Expected 1 in the line rs.Open strSQL, cnn, adOpenKeyset, adLockOptimistic. can u please help me out on this .
Hello Ankita,
Your SQL statement needs to be looked at. You may send me the file at sharma.vijay1@gmail.com to look at.
~VijaySharma
I have mailed you the file. Please take a look. Thanks
I really am intrigued by this article, however I keep crashing the program when I save it. I tried the solution from Jose and am still getting the same errors. The errors are below:
Your changes could not be saved to ‘Excel_As Database-demo-v1.xlsm” because of a sharing violation. Try saving to a different file.
When I try to save it to another file, I get the message: “The file you are trying to open, ’791CF000′,is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?”
I saved it as a new workbook and the same errors appear.
Any thoughts would be greatly appreciated.
Thanks in advance for your help
Hello Jim and Vijay,
I have the same problem. I have downloaded the demo file. When i change something in the data sheet and just click on the update dropdown button. It’s not possible to save the file any more.
Its give me the same error Your changes could not be saved to ‘Excel_As Database-demo-v1.xlsm” because of a sharing violation. Try saving to a different file.
Is there already a sollution for this problem.
Technically this provides database like query facilities. It’s not providing database capability in excel, as a spreadsheet is incapable of being a database. A spreadsheet has no concept (or ability to represent) tuples and relationships between data with primary indexes. But then most people at work don’t see any difference at all, and call a spreadsheet a database and vice-versa…happily introducing data anomalies along the way. It has been estimated that 95% of spreadsheets in workplaces contain errors…so treating a spreadsheet as a database is just one more problem of technology in hands of the litte trained.
I must say i’ve seen some doozy formulas and spreadsheets, both the WTF kind and the kind where an inordinate amount of time and money was spent to create a spreadsheet, where something else would be much better and way more efficient. spreadsheets are not scripting languages and herendous formulas get created when scripting language should be used instead. but never mind.
keep up the good work people, teaching the good stuff.
Agreed,
However, you are assuming that, when a DB is needed, one is actually available to the user. Many times this is not the case, and the data for the spreadsheet is actually required to be contained in the spreadsheet itself. I happen to be in this boat. I would much rather use proper tools for the job, they just aren’t available. This is much better than just “well, can’t be done.” Even more so, this is more like using a Ball Peen hammer when what you really need is a framing hammer. Rather than say, using a pipe wrench. At least is’t in the same family. It won’t be as effective, but it’s not a complete hack (like many of the examples you cite)
Excel to Database now supports MySQL as well as SQL Server and Access.
The tool allows you to pre-validate data and quickly upload from Excel to database -and even send data to a stored procedure!
Well worth a look!
http://leansoftware.net/Forum/default.aspx?g=topics&f=4
People…check out Microsoft’s addin for Excel called “PowerPivot”.
PowerPivot for Excel is an add-in to Excel 2010 that provides the foundation to import and combine source data from any location for massive data analysis on the desktop, including relational databases, multidimensional sources, cloud services, data feeds, Excel files, text files, and data from the Web.
The data that you add to the workbook is stored internally, as an embedded PowerPivot database inside the .xlsx file.
From a user perspective, key points are:
It allows data people to quickly create mashups of disparate data source on the fly – including web data, text files, datawarehouse files, and excel tables – and serve it up as a pivottable.
It extends the capability of Excel 2010 so that users can crunch, filter, sort millions of records with very low overhead, as well as incorporate both relational and non-relational data sources easily into analysis, using an interface they are already familiar with.
It can be programmed with SQL or DAX, but users don’t need to write SQL or DAX queries in order to use it (although they can if they want). Behind the scenes, Powerpivot creates OLAP cubes, and draws heavily on components of SQL Server to do this, but no istall of SQL Server is required.
This addin extends the capability of Excel so much that it might well remove the organisational need for some of the other apps in some cases (such as SAS) and make their licencing costs disappear.
Given Excel would be most organisation’s most widely used/deployed tool, and that this free add-in is the most significant advancement in Excel in the last decade, I think this would raise the bar on what your average analyst could do, and lower the total cost of doing it.
Further benefits of the add-in listed at http://msdn.microsoft.com/en-us/library/ee210644.aspx
Yes, I’ve been eyeing the PP plugin for some time now. Our corp won’t be migrating users to 2010 for some time though….sigh. At least it gets us time to req an sql server though. basically this technique will allow me to get much of the dev work framed before the sql server goes up, then eventually replace it with the pp plugin.
My computer doesn’t have Microsoft Activex Data ovjects 6.0 Library so how to get this
Hello Umesh,
What is the operating system installed on your system. This version is available from Windows Vista onwards.
Vijay Sharma
Now, if I could just get an example of “filtering” data, without using a pivot table, that supports multi-select filters. Vijay? got a slick solution? right now I am copying filter settings via vba to multiple pivot table, and it’s crap, slow, and more crap. I did something with some named ranges and checked boxes…it was okish
Jay – can you elaborate a bit more about what you are trying to do? I’m developing an add-in so that users can very easily filter any existing pivottable QUICKLY based on a named range i.e. “Filter pivottable A based on all the entries in range B”. I’m doing this because i’ve noticed that users spend significant amounts of time manualy filtering pivottables so that they match items in another data set.
One problem with filtering pivottables is there might be tens of thousands of pivotitems in any one field, so iterating through them can take significant time. That said, I’ve found a very smart way to iterate through them, plus am developing another approach where I don’t have to iterate through the collection at all.
However, for pivotfields with tens of thousands of items, filtering them with SQL will always be quicker.
@Jay
In addition to Jeff’s comments I often will do a pre-filter first using an Advanced Filter to get rid of the bulk of non-important data
Then run the Pivot table on the filtered data
yes, the problem is: I have a large set data sets (say that fast five times!) with about 8 common fields. The powers that be want to be able to filter on about 6 of those at any given time. MOST of the filters are restricted to about a dozen or so, however there are a few that number closer to 100. and of course, they want:Ability to multi-select, all pivot filters linked to a master filter. I’ve used a variety of vb and other techniques to link the filters, but of course copying those 100ish items to 6-10 other pivot tables ultimately is time consuming. I’d much rather use this sql technique, but I don’t know how to support the multi-select. I have done it in the past by using the pivot_change event, duping all the filter settings to a table, then using a formula in the data for each table to set “in scope”. that filter setting is pre-set to “true” for all pivots, and hidden. its better, but not good. The most efficient i have done is to put the data for each of the “100ish” into separate tabs, use sum-ifs on the main page, and move “out of scope” tabs outside the sum-if tab range, again a kludge (but surprisingly the fastest method) I think I could apply this method using a tree-view control as the input(s) rather than a select box… In reality, we shouldn’t be using excel as our presentation layer, but alas, we are!
@Jay they want [the] ability to multi-select, all pivot filters linked to a master filter. If they have Excel 2010, then slicers are a damn easy way to do this – assuming all pivots run off the same cache. (That said, there’s a way around that that I want to try out). Otherwise, Debra has some code at http://blog.contextures.com/archives/2012/01/03/change-all-pivot-tables-with-one-selection/ that may help (although it might take significant time to iterate through each pivottable and each pivot field, and each pivotitem)
I don’t know how to support the multi-select Sound’s like a simple WHERE IN clause would do the trick.
Can you post a sample spreadsheet somewhere for me to have a look at?
Yes, I’d love for them to move to 2010, then I’d go with the Power Pivot plugin and move the data to an sql server that we should be getting soonish. I have used the code you linked, or a variant. It’s slow, but works. I actually use a different “hack” I have found a little faster for large filter sets, but it’s not pretty. I have been looking at this method. I like it, since 2 of the large filters are interdependant (depending on what is set on 1, I can reduce the second filter choices significantly) I just need to figure out an elegant way to do multi-select. I think I have. I can, in theory use a TreeView control. The ListBox control with mulit-select also might work well, particularly for the “non-interdependent” filters. Then build up the SQL, probably with an “Apply” button. I’ll let you know if I get it to work well.
HI,
I am a novice on excel vba, i used to complete the task whatever assigned to me by searching google, on searching i found this link and it is very very useful. The query which i got was in the attachment attached in this post the product field got some product related information but if i put Date in its place it didn’t filtered out!!. can you please advice how can it be achieved?? and also advice us how can we proceed the above function with only one key say date field alone(in this tutorial u used 3 keys).
Hi,
i am completely untouched by programming (if you dont count in some classes many years ago), and my goal is to create stock database. For filtering of what i have in stock (and movements) i can use yours tutorial to filter out data i need to see, but what about adding new rows to data sheet? It is possible to do it in some kind of dialogue box?
Thanks for help.
@Bee_Boo, perhaps you could take advantage of Excel-to-database to insert new rows?
http://leansoftware.net/en-us/productsdownloads/exceltodatabase.aspx
Vijay Sir
Like jim said on 24-04-2012 I am also getting same errors repeatedly with some Alphanumeric Name generated excel file… Is this due to using excel as database??
2) Will the data be lost if we use this code & will there be any problem to our computer for same….
Why this type of errors are coming ?? Please Clarify
Hello Vrunda,
Kindly share your workbook with me at sharma.vijay1@gmail.com to look at and figure out what is going wrong.
~VijaySharma
I have the same problem. I have downloaded the demo file. When i change something in the data sheet and just click on the update dropdown button. It’s not possible to save the file any more.
Its give me the same error Your changes could not be saved to ‘Excel_As Database-demo-v1.xlsm” because of a sharing violation. Try saving to a different file.
Also the problem when you change something in the data sheet. click reset and click update dropdown button. than choose products and show data. Its giving me the old data. The database is not been reset or reloaded.
I found a workaround: Update the data sheet with your query than save close the file open the file and than update drop down.
Now you have the full functionality
Hi Vijay,
Can you provide me the VBA code to read from multilpe excel files to a single excel sheet
Thanks,
Binu
Hi Vijay,
Thanks for sharing this work with us novices.
I have modifed the combo box names to suit my data table but get run-time error, type mismatch when update dropdowns button is pressed.
Private Sub cmdUpdateDropDowns_Click()
strSQL = “Select Distinct [Card] From [data$] Order by [Card]”
closeRS
OpenDB
cmbCard.Clear
rs.Open strSQL, cnn, adOpenKeyset, adLockOptimistic
If rs.RecordCount > 0 Then
Do While Not rs.EOF
cmbCard.AddItem rs.Fields(0)
rs.MoveNext
Loop
Else
MsgBox “I was not able to find any unique Card.”, vbCritical + vbOKOnly
Exit Sub
End If
Dear Vijay,
Really helps me a lot with this.
Could you help me how to add code for Manipulating Headings of Data.
What I was trying to achieve is aside from the sample data$ (say sheet1), I need to display datas from sheet2. The content and headings are not exactly the same.
In shot I need code to manipulate Headings from data that I was trying to view.
A new version of Excel Database Tasks V2 has been released : please see blog
http://leansoftware.net/forum/en-us/blog.aspx
Regards
Thank You very very very very very very very very very very very very very Muchhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh
its really excellent
You are the best. I get so much out of your examples and explanations. I plan on signing up for your training in the next few months. But first I need some help for I project I a m working on. I have data tables in different tabs within the same workbook. How do I use VBA or other tools to get them to export the tabs as Access tables without leaving Excel? Assume no Access tables exist and that I have two scenarios for storing the Access tables:
1) I do this every month and want to keep the monthly access tables seperate.
2) I do this every month and want to update the same table.
Thanks.
Hi Jijay,
I have learn so much from your demo file- Excel as Datase. I have motified the vba code to fit my dataset for my work. However, I run to the problem when I try to save the file after clicking the “Show Data” button. Its give me the same error “Your changes could not be saved to ‘Excel_As Database-demo-v1.xlsm” because of a sharing violation. Try saving to a different file. ” I have seen other viewers post the same issue but have not found a solution be posted yet. Please advise how I can fix the problem? Thank you.
Hello everyone,
Thank you for this method!
Any answer regarding Louis’s issue (posted on Sept 9th 2012).
I encounter the same pb (run time error 13 – type mismatch).
Best regards
@Cho07tom and Louis, Please look for blank records in the column that you are getting as a result of the SQL query, this is causing the issue. Please try the below code
strSQL = "Select Distinct [Product] From [data$] Order by [Product]"
closeRS
OpenDB
cmbProducts.Clear
rs.Open strSQL, cnn, adOpenKeyset, adLockOptimistic
If rs.RecordCount > 0 Then
Do While Not rs.EOF
If Not IsNull(rs.Fields(0)) Then cmbProducts.AddItem rs.Fields(0)
rs.MoveNext
Loop
Else
MsgBox "I was not able to find any unique Products.", vbCritical + vbOKOnly
Exit Sub
End If
Let me know if you need any further assistance.
~VijaySharma
Hello Vijay,
It perfectly works!
Many thanks for your help.
Regards,
cho7tom
Am trying to use this method in my workbook.
The example book works fine.
When I change to my own book, however, I get a 446 when it is executing the cnn.ConnectionString line in the OPENDB sub.
I have selected the activeX library in the references.
Any ideas ?
Please ignore the post I made – it is resolved now.
Using SQL in Excel – how do I address the columns in a sheet without giving each column a name ?
For example, I need to select the 3rd and 4th columns of a sheet and do not want to give them a name ( as the sheets are from read-only work books which do not have named columns )
I have tried “Select c,d…” which does not work.
@Old Fogey,
In that case the first row is treated as column headings.
~VijaySharma
I see a lot of people saying Excel shouldn’t be used as a DB yet they never say why. To me, it seems they just want to keep DB administration so complex that it becomes necessary to keep a DB admin on staff. Excel, is understood by a larger group of people and is more approachable.
Access sometimes creates unnecessary duplication with multiple references spread out on multiple tables whereas with Excel, a simple common reference allows for a leaner DB.
So, good on you Vijay to put forward that Excel can be used as a DB. As long as you can archive rarely used data and then have ability to retreive it, there shouldn’t be a problem.
Well, one reason to use Excel as a database is when your “user” bought a Home and Student version of Office. They don’t have Access to use as a database because it’s not included. I can’t tell someone to buy Access because they may not have the financial means to do so. So for a very, very simple and small database system (about 1500 records) Excel will do. It’s not as convenient as Access, but nevertheless useful….
I am a non-computer background, am doing a research in medical(microbiology), i need a simple database with excel back end, can u send the details… my requirement is receiving sample and entering to the database with id number & date, oftenly i have to check with id number whether the id number received or not.
Sounds like you meant to say a simple database with excel Front End.
that said, if you have a small data set, you could just use an excel table (let’s call it TableMyMicrobeData to hold the data, and your charts or whatever (pivot tables?) would use as the source.
Otherwise, since it sounds like you have office, you could use an access database and a data connection via MSQuery in excel. both have enough “visual” tools, you could complete your database-excel report without writing any code at all.
Please Vijay,
can u be a little more step by step, as like in other tutorials on this site.
i am totally new to this SQL thing and whenever u try to make new database, as u did, or modify ur database, i am getting errors.
jst need steps as how to modify basic names and if some1 want to add 1 more filter/search box
thank you.
Hi,
just to let you know Excel-to-database has become ‘Excel Database Task’s (EDT) and allows very easy Data Edit of a database Table from Excel.
Please see blog for new features:
http://leansoftware.net/forum/en-us/blog.aspx
Shortly to include drop down lists for relational data.
Thank you for the interest
Hi Chandoo,
I am new to totally new to VBA or any programming for that matter,
I am using Excel to do my Quotes and Invoices. In the same workbook, I want to start a database to show a history of quotes for the month/year, without losing any data. I would also like to possibly add a macro for when I print my quote, to only save the Quotation worksheet and not the whole file, but also update the database automatically.
The Database could also be used to pull information for invoicing purposes.
Would you require me to send through my workbook so you can get a better understanding of what I am talking about.
How would I go about doing this?
Your help will really be appreciated, Thanks.
Can i buy a template for this ss?
I have tried to create it, starting on the VBA and Macros stuff but couldnt even make a cell go red for some reason.
Thanks
Awesome Job. I have question.
in View sheet.
When it putts data on sheet starting from row 12.
What is the easiest way to Sort by column I then G.
Thanks a lot for sharing.
Hi
My English is not my database. And therefore all its characters? Appear. What can I do?
plz
help me….
My English is not my database. And therefore all its characters? Appear. What can I do?
Is there any way to handle nulls within a SQL statement in Excel VBA ? It does not recognize the nz() function
Love this! In many of our systems and reports, we work with an OLAP database called Cognos TM1. Excel and TM1 Excel formulas are really the most common interface to that database.
Because of that and the fact that most of our work ends up being in Excel pages itself anyway, I can really see using this Excel as a Database functionality becoming really useful for so many posibilities. This also will finally allow us not to bring in yet a third application platform like Access or Oracle in our Excel Datamarts.
I undersood the logic of connecting the sheets but i didnt understand how to build the second sheet i.e. view sheet…….
Please help me in building the second sheet…….. i have written the code as you said in this post but unable to get the file as u r showing in thi spost…
how to get the view part…….
Thanks for this nice tutorial.
Hope you don’t mind if I make some remarks.
I would highly recommend:
1. NO need of a module and above all NO PUBLIC variables, if there is another workbook with the same variables you’ll get an error!
Move your code from ‘module1′ to the sheet ‘View’ and declare the variable like this:
Option Explicit
Dim cnn As ADODB.Connection’ or Private…
Dim rs As ADODB.Recordset
Dim strSQL As String ‘ could be repeated at Procedure level…
2.Other changes:
Private Sub OpenDB()
Set cnn = New ADODB.Connection
Private Sub cmdShowData_Click()
Set rs = New ADODB.Recordset
Each time you open a recordset, disconnect it. How many instances of the connection do you create???
rs.Open strSQL, cnn, adOpenKeyset, adLockOptimistic
Set cnn = Nothing ‘ disconnect the recordset and dispose the connection
At each place where you exit a sub, dispose the recordset!
Set rs = Nothing ‘dispose the recordset
Exit Sub
Start using a discipline in programming that follows the OOP rules…
Hope it helps,
Best Wishes,
Bernard
Another point:
You will see that the format is not the same, no $ sign after a few rows, different alignment…
This will help:
‘Now putting the data on the sheet
ActiveCell.CopyFromRecordset rs
‘Add this to have the same format
With Range(“dataSet”)
.Select
.Copy
End With
Range(Selection, Selection.End(xlDown)).PasteSpecial (xlPasteFormats)
Application.CutCopyMode = False’ clear the clipboard
Range(“dataSet”).Select
Best Wishes,
Garouda.
Well, I fear some people may get confused. I prefer to give the whole code here.
remark: remove module1
Option ExplicitDim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strSQL As String
Private Sub OpenDB()
Set cnn = New ADODB.Connection
If cnn.State = adStateOpen Then cnn.Close
cnn.ConnectionString = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" & _
ActiveWorkbook.Path & Application.PathSeparator & ActiveWorkbook.Name
cnn.Open
End Sub
Private Sub cmdReset_Click()
'clear the data
cmbProducts.Clear
cmbCustomerType.Clear
cmbRegion.Clear
Sheets("View").Visible = True
Sheets("View").Select
Range("dataSet").Select
Range(Selection, Selection.End(xlDown)).ClearContents
End Sub
Private Sub cmdShowData_Click()
Set rs = New ADODB.Recordset
'populate data
strSQL = "SELECT * FROM [data$] WHERE "
If cmbProducts.Text "" Then
strSQL = strSQL & " [Product]='" & cmbProducts.Text & "'"
End If
If cmbRegion.Text "" Then
If cmbProducts.Text "" Then
strSQL = strSQL & " AND [Region]='" & cmbRegion.Text & "'"
Else
strSQL = strSQL & " [Region]='" & cmbRegion.Text & "'"
End If
End If
If cmbCustomerType.Text "" Then
If cmbProducts.Text "" Or cmbRegion.Text "" Then
strSQL = strSQL & " AND [Customer Type]='" & cmbCustomerType.Text & "'"
Else
strSQL = strSQL & " [Customer Type]='" & cmbCustomerType.Text & "'"
End If
End If
If cmbProducts.Text "" Or cmbRegion.Text "" Or cmbCustomerType.Text "" Then
'now extract data
OpenDB
rs.Open strSQL, cnn, adOpenKeyset, adLockOptimistic
Set cnn = Nothing
If rs.RecordCount > 0 Then
Sheets("View").Visible = True
Sheets("View").Select
Range("dataSet").Select
Range(Selection, Selection.End(xlDown)).ClearContents
'Now putting the data on the sheet
ActiveCell.CopyFromRecordset rs
With Range("dataSet")
.Select
.Copy
End With
Range(Selection, Selection.End(xlDown)).PasteSpecial (xlPasteFormats)
Application.CutCopyMode = False
Range("dataSet").Select
Else
MsgBox "I was not able to find any matching records.", vbExclamation + vbOKOnly
Set rs = Nothing
Exit Sub
End If
'Now getting the totals using Query
If cmbProducts.Text "" And cmbRegion.Text "" And cmbCustomerType.Text "" Then
strSQL = "SELECT Count([data$].[Call ID]) AS [CountOfCall ID], [data$].[Resolved] " & _
" FROM [Data$] WHERE ((([Data$].[Product]) = '" & cmbProducts.Text & "' ) And " & _
" (([Data$].[Region]) = '" & cmbRegion.Text & "' ) And (([Data$].[Customer Type]) = '" & cmbCustomerType.Text & "' )) " & _
" GROUP BY [data$].[Resolved];"
OpenDB
rs.Close
rs.Open strSQL, cnn, adOpenKeyset, adLockOptimistic
Set cnn = Nothing
If rs.RecordCount > 0 Then
Range("L6").CopyFromRecordset rs
Else
Range("L6:M7").Clear
MsgBox "There was some issue getting the totals.", vbExclamation + vbOKOnly
Set rs = Nothing
Exit Sub
End If
End If
End If
rs.Close
Set rs = Nothing
End Sub
Private Sub cmdUpdateDropDowns_Click()
strSQL = "Select Distinct [Product] From [data$] Order by [Product]"
OpenDB
cmbProducts.Clear
Set rs = New ADODB.Recordset
rs.Open strSQL, cnn, adOpenKeyset, adLockOptimistic
Set cnn = Nothing
If rs.RecordCount > 0 Then
Do While Not rs.EOF
cmbProducts.AddItem rs.Fields(0)
rs.MoveNext
Loop
Else
MsgBox "I was not able to find any unique Products.", vbCritical + vbOKOnly
Set rs = Nothing
Exit Sub
End If
'----------------------------
strSQL = "Select Distinct [Region] From [data$] Order by [Region]"
OpenDB
cmbRegion.Clear
rs.Close
rs.Open strSQL, cnn, adOpenKeyset, adLockOptimistic
Set cnn = Nothing
If rs.RecordCount > 0 Then
Do While Not rs.EOF
cmbRegion.AddItem rs.Fields(0)
rs.MoveNext
Loop
Else
MsgBox "I was not able to find any unique Region(s).", vbCritical + vbOKOnly
Set rs = Nothing
Exit Sub
End If
'----------------------
strSQL = "Select Distinct [Customer Type] From [data$] Order by [Customer Type]"
OpenDB
cmbCustomerType.Clear
rs.Close
rs.Open strSQL, cnn, adOpenKeyset, adLockOptimistic
Set cnn = Nothing
If rs.RecordCount > 0 Then
Do While Not rs.EOF
cmbCustomerType.AddItem rs.Fields(0)
rs.MoveNext
Loop
Else
MsgBox "I was not able to find any unique Customer Type(s).", vbCritical + vbOKOnly
Set rs = Nothing
Exit Sub
End If
rs.Close
Set rs = Nothing
End Sub
Best Wishes,
Garouda
I noticed another potential issue.
The combo boxes are set as 0-fmStyleDropDownCombo.
Look at Properties, Style, in Design Mode…
This enables the user to add some item in the combo.
I would rewrite the Reset code like this:
Private Sub cmdReset_Click()'clear the data
cmbProducts.Clear
cmbProducts.Text = vbNullString' in case a user wrote something
cmbCustomerType.Clear
cmbCustomerType.Text = vbNullString
cmbRegion.Clear
cmbRegion.Text = vbNullString
Sheets("View").Visible = True
Sheets("View").Select
Range("dataSet").Select
Range(Selection, Selection.End(xlDown)).ClearContents
Me.[C4].Select ' no visible selection
End Sub
Best Wishes,
Garouda
Is there a way to solve the issue that prevents saving. I get a sharing violation if I try to save the file after making changes. Should I implement the database based on the comments from Garouda?
Garouda, your code worked and I can now save the file.
Thanks for posting it.
Hi Colin,
One possible reason is that the file is ‘read only’. What you have to do is to save it under another name.
!One little change in the code: to avoid any problem, it would be better to replace
rs.close
by
If rs.State = adStateOpen Then rs.Close
at the end of the procedure
cmdShowData_Click
Indeed, if you do not select anything and click on the command button Show Data, it’ll generate an error.
Basically the program should have some safety features to prevent this.
I’ll made some changes in order to take that into account…
Another remark, I never declare variables at module level if I can avoid it. Declare them at procedure level each time you can. You will see that I send the connection to the OpenDB procedure By Reference ByRef.
There are two possibilities: ByVal or ByRef, ByRef is the default value in VBA and VB6, not in VB.Net anymore. In this case, only ByRef is possible. ByVal sends a copy of the variable to a procedure or a function where ByRef works like a pointer in C or C++. ByRef tells the programme where to find the object in the computer memory…
I’m going to copy the whole modified code again in a next post.
Try to click on ‘Show Data’ in my previous code, without selecting anything, and try it with the new code.
Cheers,
Garouda
Here the modified code, more bullet proof…
Cheers,
Garouda
Do not forget to remove Module1 and replace the code in the sheet ‘View’ with this one:
Option ExplicitDim niFlag As Integer
Private Sub OpenDB(ByRef cnn As ADODB.Connection)
'note the change between brackets ByRef cnn As...
cnn.ConnectionString = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" & _
ActiveWorkbook.Path & Application.PathSeparator & ActiveWorkbook.Name
cnn.Open
End Sub
Private Sub cmdReset_Click()
'clear the data
cmbProducts.Clear
cmbProducts.Text = vbNullString
cmbCustomerType.Clear
cmbCustomerType.Text = vbNullString
cmbRegion.Clear
cmbRegion.Text = vbNullString
Sheets("View").Visible = True
Sheets("View").Select
Range("dataSet").Select
Range(Selection, Selection.End(xlDown)).ClearContents
Me.[C4].Select ' more professional: the active cell is hidden...
niFlag = 0
End Sub
Private Sub cmdShowData_Click()
'it's always better to declare such variables at procedure level
Dim strSQL As String
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
If niFlag = 0 Then
MsgBox "Please populate the lists first - click on Update Drop Downs!", vbOKOnly + vbExclamation, "Excel and ADODB"
Exit Sub
End If
If cmbProducts.Text = vbNullString And cmbCustomerType.Text = vbNullString And cmbRegion = vbNullString Then
MsgBox "Select at least one item!", vbOKOnly + vbExclamation, "Excel and ADODB"
Exit Sub
End If
Application.ScreenUpdating = False
'create a new instance of the recordset
Set rs = New ADODB.Recordset
'populate data
strSQL = "SELECT * FROM [data$] WHERE "
If cmbProducts.Text "" Then
strSQL = strSQL & " [Product]='" & cmbProducts.Text & "'"
End If
If cmbRegion.Text "" Then
If cmbProducts.Text "" Then
strSQL = strSQL & " AND [Region]='" & cmbRegion.Text & "'"
Else
strSQL = strSQL & " [Region]='" & cmbRegion.Text & "'"
End If
End If
If cmbCustomerType.Text "" Then
If cmbProducts.Text "" Or cmbRegion.Text "" Then
strSQL = strSQL & " AND [Customer Type]='" & cmbCustomerType.Text & "'"
Else
strSQL = strSQL & " [Customer Type]='" & cmbCustomerType.Text & "'"
End If
End If
If cmbProducts.Text "" Or cmbRegion.Text "" Or cmbCustomerType.Text "" Then
'now extract data
'create a new instance of the connection
Set cnn = New ADODB.Connection
'note that we have to add cnn
OpenDB cnn
'create the recordset
rs.Open strSQL, cnn, adOpenKeyset, adLockOptimistic
'dispose the connection and disconnect the recordset
Set cnn = Nothing
'DO NOT close the connection here as it'll close the recordset as well
If rs.RecordCount > 0 Then
Sheets("View").Visible = True
Sheets("View").Select
Range("dataSet").Select
Range(Selection, Selection.End(xlDown)).ClearContents
'Now putting the data on the sheet
ActiveCell.CopyFromRecordset rs
With Range("dataSet")
.Select
.Copy
End With
Range(Selection, Selection.End(xlDown)).PasteSpecial (xlPasteFormats)
Application.CutCopyMode = False
Me.[C4].Select
Else
MsgBox "I was not able to find any matching records.", vbExclamation + vbOKOnly
Set rs = Nothing
Exit Sub
End If
'Now getting the totals using Query
If cmbProducts.Text "" And cmbRegion.Text "" And cmbCustomerType.Text "" Then
strSQL = "SELECT Count([data$].[Call ID]) AS [CountOfCall ID], [data$].[Resolved] " & _
" FROM [Data$] WHERE ((([Data$].[Product]) = '" & cmbProducts.Text & "' ) And " & _
" (([Data$].[Region]) = '" & cmbRegion.Text & "' ) And (([Data$].[Customer Type]) = '" & cmbCustomerType.Text & "' )) " & _
" GROUP BY [data$].[Resolved];"
Set cnn = New ADODB.Connection
OpenDB cnn
rs.Close
rs.Open strSQL, cnn, adOpenKeyset, adLockOptimistic
Set cnn = Nothing
If rs.RecordCount > 0 Then
Range("L6").CopyFromRecordset rs
Else
Range("L6:M7").Clear
MsgBox "There was some issue getting the totals.", vbExclamation + vbOKOnly
Set rs = Nothing
Exit Sub
End If
End If
End If
rs.Close
Set rs = Nothing
Application.ScreenUpdating = True
End Sub
Private Sub cmdUpdateDropDowns_Click()
Dim strSQL As String
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
niFlag = 1
strSQL = "Select Distinct [Product] From [data$] Order by [Product]"
Set cnn = New ADODB.Connection
OpenDB cnn
cmbProducts.Clear
Set rs = New ADODB.Recordset
rs.Open strSQL, cnn, adOpenKeyset, adLockOptimistic
Set cnn = Nothing
If rs.RecordCount > 0 Then
Do While Not rs.EOF
cmbProducts.AddItem rs.Fields(0)
rs.MoveNext
Loop
Else
MsgBox "I was not able to find any unique Products.", vbCritical + vbOKOnly
Set rs = Nothing
Exit Sub
End If
strSQL = "Select Distinct [Region] From [data$] Order by [Region]"
Set cnn = New ADODB.Connection
OpenDB cnn
cmbRegion.Clear
rs.Close
rs.Open strSQL, cnn, adOpenKeyset, adLockOptimistic
Set cnn = Nothing
If rs.RecordCount > 0 Then
Do While Not rs.EOF
cmbRegion.AddItem rs.Fields(0)
rs.MoveNext
Loop
Else
MsgBox "I was not able to find any unique Region(s).", vbCritical + vbOKOnly
Set rs = Nothing
Exit Sub
End If
'----------------------
strSQL = "Select Distinct [Customer Type] From [data$] Order by [Customer Type]"
Set cnn = New ADODB.Connection
OpenDB cnn
cmbCustomerType.Clear
rs.Close
rs.Open strSQL, cnn, adOpenKeyset, adLockOptimistic
Set cnn = Nothing
If rs.RecordCount > 0 Then
Do While Not rs.EOF
cmbCustomerType.AddItem rs.Fields(0)
rs.MoveNext
Loop
Else
MsgBox "I was not able to find any unique Customer Type(s).", vbCritical + vbOKOnly
Set rs = Nothing
Exit Sub
End If
If rs.State = adStateOpen Then rs.Close
Set rs = Nothing
End Sub
Needs to make littlle correction in source in case if you need to see all data: strSQL = “SELECT * FROM [data$] WHERE ” instead use
strSQL = “SELECT * FROM [data$]” and move where to if statement
change.
Plus it adds no value to excel filter.
This may be used when OR,Join functionality is needed from database.but MSquery muchmore dynamic and less cumbersome.
This is a great article as always and I think it could really help with a regular report I need to setup in work. Could you help by telling me how I could add a second table and link both using a unique identifier?