Stoopid Microsoft

It used to be so easy … I did it before using Delphi C++, admittedly a low-tech (by today’s standards, at least) program, but it worked.

All I want to do is write a visual basic program using VB 2008 to access a Microsoft Excel .accdb database. Is that too much to ask?

But now you’ve got oleDB and this and that and there seems to be no simple way in Microsoft’s extensive knowledge base that simply shows you how to do it. And most tutorials out there use older version of VB that just don’t work anymore under 2008.

Maybe it’s me. Maybe I’m the stoopid one. But it worked before. Why do they have to make things so damned difficult.

Stoopid Microsoft. Or stoopid me. I’m still trying to decide.

What’s a “Microsoft Excel .accdb database”?

See. I’m stoopid. I meant an Access database with an .accdb extension. Oops.

Yes, that should be easy, and should be easy to find - it’s a really basic thing to do.

You have my sympathies. Let me guess…the new version of VBA has helpfiles that are 1) inaccurate, 2)non-existent, 3)poorly indexed, or 4)all of the above. Just like the helpfiles for the version before last.

This would be one of the reasons that I’ve refused to switch to 2007. I don’t know how long I can hold out, but I’m giving it my best.

Sorry, couldn’t resist :slight_smile:
Have you tried asking on the Technet forums? There are varied reactions regarding their value, but some people do find them helpful.

Six years ago, I took a class in Access 2003/Visual Basic 6. I was very good at it - Loved the program, was able to do the forms, macros, cases, sorts, queries - all the fun stuff. I made some nifty little programs that did some routine tasks for me.

I came up with some tracking issues a month ago, and decided that this was a perfect opportunity to whip out a little database and impress my boss with some technical skills.

Since this is the pit, I can say this - Nertz to VB.net! They changed the language, they changed references, they changed every damn thing juuuuuust enough to make my skills useless, yet still seem like they should work.

I wish Microsoft would just keep their products standard, and stop making upgrades for no apparent reason - Office 2007, I’m looking at you. :mad:

I haven’t yet … I’m keen to find an answer myself.

Keen … or stubborn … take your pick.

How about google? Type “VB example code accdb” and I bet you find what you want in 10 seconds or less.

When you’re done being keen and/or stubborn :), go here for a quick and dirty example. It’s really pretty easy, but is different from DAO, RDO, and even the original flavor of ADO, whichever you were using back in the day.

Don’t count on it. All I wanted to do was get the cell address assigned to a data point in a chart so I could change it’s color based on the data. Seems like a basic thing to do right? No amount of googling could solve that one. Changing the color was easy. Reading the data was impossible as far as I could tell.

So I figured the data I wanted was in each data point on the chart anyway so I didn’t need to get the cell address. I could just read it off the chart. How hard could that be? Surely they’d let you read data points easily. I mean it’s bloody obvious you’d sometimes want to actions based on chart conditions, right? Right?
WRONG MOTHER FUCKER.
Three rage filled hours later I finally came up with this:
Dim mavars As Variant
mavars = Sheets(“Time Available”).ChartObjects(“Clock” + CStr(j)).Chart.SeriesCollection(1).Values

I’ll be honest. I don’t think you’re really supposed to be able to read chart values. No amount of googling could even find discussion of it in which functional code was produced. The help documentation as patchy, vague, and horribly organized.
The full code [spoiler](posted because I need to brag dang it, and incase someone smarter then me can give me tips to improve it I could learn from)



Sub cleanyerclocks()
    For j = 1 To 14 Step 1
        For i = 1 To 12 Step 1
            Dim mavars As Variant
            mavars = Sheets("Time Available").ChartObjects("Clock" + CStr(j)).Chart.SeriesCollection(1).Values
            Select Case mavars(i)
                Case Is = 1
                    Sheets("Time Available").ChartObjects("Clock" + CStr(j)).Chart.SeriesCollection(1).Points(i).Interior.ColorIndex = 43
                Case Is = 1.01
                    Sheets("Time Available").ChartObjects("Clock" + CStr(j)).Chart.SeriesCollection(1).Points(i).Interior.ColorIndex = 29
                Case Is = 1.02
                    Sheets("Time Available").ChartObjects("Clock" + CStr(j)).Chart.SeriesCollection(1).Points(i).Interior.ColorIndex = 32
                Case Is = 1.03
                    Sheets("Time Available").ChartObjects("Clock" + CStr(j)).Chart.SeriesCollection(1).Points(i).Interior.ColorIndex = 46
            End Select
        Next i
    Next j
End Sub


What it does is:
I have two spread sheets. A visable one with my weekly schedule and a hidden one. The hidden one has mirror cells of the visable one. Each one has nested if statements in that says ( for example the one from C23) “=IF(‘Time Available’!C23=“Available”,1,IF(‘Time Available’!C23=“In Class”,1.01,IF(‘Time Available’!C23=“Drive Time”,1.02,1.03)))”

What this does is make it so I can make a chart for each block of 12 hours. The chart will look like a clock since it’ll have 12 sections that will appear to be the same size. The slight difference in number is invisible to the eye, but my macro can see it and take action accordingly. Using a 1-12 filled range for the legend serious gives me the clock numbers. I put the clocks on the visible sheet, with a slight rotation so 12 is on top, like a clock. 12 is kinky like that.

Then I assigned the macro to one of the clocks.

Then I can fill in my weekly schedule, click the trigger clock and it automatically colors in the clocks based on what I’m doing at each time.
[/spoiler]

I had to give my self a crash course in VB to program that. Did it in 6 hours starting with no knowledge of VB at all. Mot of that six hours was spent trying to figure out how to read the bloody chart data point data.

I made it because I wanted something cool to whizbang them at a job interview yesterday.
*f-bomb meant only as intensifier not an insult.

No offense, but (assuming I’m understanding your problem correctly) that seems like a totally back-asswards way to do things. It’s like trying to scrape data from a report instead of just querying the database. I’m not surprised there wouldn’t be a simple built-in API for it.

While I do agree that the online documentation is shite (I particularly like the page that lists the Chart object’s members), I’m not sure I see the problem with what you’ve typed there, though. Most of it is just identifying the particular object you need, in a fairly sensible (albeit hard to discover) hierarchical structure. Far better than going online for this sort of thing is to use the object browser in the editor (hit F2 and you can browse or search for things). There you can see the members and properties of all the classes, most of which have at least somewhat self-explanatory names.

Also, using proper type declarations gets you a bit more autocomplete information; Variants are often useful, but it means the editor can’t work out what data type you’re using, so can’t give you hints on what members or properties it has.

[spoiler]So in your example, you might do something like this:



Sub cleanyerclocks()
    Dim ClockSeries As Series
    For Each ClockChtObj In Sheets("Time Available").ChartObjects
        Set ClockSeries = ClockChtObj.Chart.SeriesCollection(1)
        With ClockSeries
            For i = 1 To 12 Step 1
                Select Case .Values(i)
                    Case Is = 1
                        .Points(i).Interior.ColorIndex = 43
                    Case Is = 1.01
                        .Points(i).Interior.ColorIndex = 29
                    Case Is = 1.02
                        .Points(i).Interior.ColorIndex = 32
                    Case Is = 1.03
                        .Points(i).Interior.ColorIndex = 46
                End Select
            Next i
        End With
    Next ClockChtObj
End Sub


This way you ought to get autocomplete information every time you type a period within the scope of the With operator, which also helps cut down on those lengthy strings of objects. The For Each … In construction also makes things more readable, IMO, because it’s much clearer what you’re iterating over (although the example I give will iterate over all charts on the sheet, so if you’ve got other, non-clock charts on it, you’ll need to put a check to make sure you don’t change those).

But yeah, learning the Excel object model is very much an exercise in frustration, and the quicker you give up on the MSDN documentation, the happier you’ll be.[/spoiler]
MrSquishy, I don’t think it’s as counter-intuitive as you make out. He’s formatting a Chart, so is already working within the context of the Chart’s data (which is actually pretty easily accessible once you know how). While accessing the cell data might be easier on its own, you’d then have the problem of mapping it to the relevant point in the chart. So I’d say it actually makes much more sense to look at the Series. Apart from anything else, it’ll mean your code works no matter where the chart pulls its data from, instead of being dependent on the sheet’s particular layout.

MSDN is pants. I find it’s all ‘waffle waffle waffle’ and no ‘here’s how to do stuff’

Well the database for each chart was various ranges in a spreadsheet. There was 14 separate charts. Each chart had it’s own range.

I thought about manually coding in the ranges for each chart but that would have practical issues. As well as be ugly and inelegant.

Say for some reason I, or someone using it decides to decided to insert a row in the charts. Excel should automatically update all, or atleast most of, the cells, and chart ranges that point to the moved cells.

What it won’t do is update my script. Suddenly my script is reading data from the wrong places. Which means I have to go in and fix 14 separate ranges.

Now you might make the correct point that I’m the only one likely to use it and I prolly won’t be moving it around. However I feel it’s good practice to write code that can cope with as many unexpected conditions as possible. If nothing else it’s practice for when I need to make a chart reading script that will have all sorts of weird stuff happen to it.

Plus despite the frustration it was an learning experience and I’d do it all over again. Plus it was a fun challenge.
Which brings me to Dead Badger. If I understand correctly, and have the right words, your code assigns the Series object type to ClockSeries so it inherits all the Series objects and methods. That’s pretty cool you can do that. I always thought Visual Basic was more, well more basic then that. Also the For Each looks a lot cleaner for walking through graphs and things then a loop. Didn’t know about that either. True unrelated charts would mess it up, but since the clock charts all are named “clock” with number on the end then checking with something like this should keep it on target (untested, prolly doesn’t work as is but hopefully shows what I mean):



                Select Case .Name
                    Case Is != "Clock#"
                        Next i
                End Select


Anyway thanks. I learned quite a bit.

Er that should be Next ClockChtObj in the code.:smack:

Bolding mine.

Just because you don’t get it doesn’t mean it’s useless.

You really shouldn’t expect anything you were using six years ago to still be relevant now. If you really want to go back to 2003 and keep storing data in access (and you were behind the times then, too) then go right ahead.

You’re not allowed to bitch when technology (specifically, development technologies) leaves your ass behind. It’s your job as a developer to either keep up, or drive the tech yourself.

Well, for a single test I wouldn’t use a Select statement - a simple If … Then / End If will do. But yeah, that’s the sort of thing you want. You’ll have to use InStr() as well; string comparisons don’t work with the basic operators like = and !=. So if you wanted to check that a string started with “Clock” you’d check that InStr( 1, TestStr, “Clock” ) = 1 (Meaning that the string “Clock” occurs in string TestStr at the first position, searching from the start). E.g.:


For Each ClockChtObj in blah
    If InStr( 1, ClockChtObj.Name, "Clock" ) = 1 Then
        <do stuff>
    End If
Next ClockChtObj

Right, that’s enough geeking for the Pit, I think. :slight_smile:

Why? I’ve been writing C for a hell of a lot longer than six years and the vast majority of it is still perfectly relevant to people now. The POSIX API hasn’t changed in decades. The standard C89 API hasn’t changed in 20 years! Ditto a lot of what the non-MS world is doing with C++, Java, Perl, Python, Ruby, and so on.

This is why I hate closed-source software. The software maker can keep developers on the upgrade cycle because they own the software and they control the standard to which the software is written.

In the open source world, open standards are how things get done. Everyone knows what the rules are and everyone is allowed to implement tools to work with the data and the code. Any developer who tried to arbitrarily change things in an incompatible way would be quietly ignored.