Author Topic: Access NOOB - or what should I do?  (Read 2421 times)

0 Members and 1 Guest are viewing this topic.

CmdrDuh

  • Automatic Duh Generator
  • King Gator
  • Posts: 4039
Access NOOB - or what should I do?
« on: January 30, 2007, 08:57:45 AM »
In a nutshell, it comes down to Access vs. Excel. 

So heres what Im doing, I am trying to extract attribute data out of all the blocks in a drawing, to build a BOM that includes the standard stuff, cost, description, manhours to install, etc.  I currently have a working solution in excel, but I had a bit of a problem last week.  I replaced all the "test" data with real data, and all the formulas and lookup tables just quit working.  Thats it, just quit working. :cry:

I spent the better part of 2 days trying to get a lookup table to start working.  I even tried creating a brand new file, and writing the look up from scratch.  Well eventually, it started working again.  I dont know why, it just did. :ugly:

So I got to thinking, maybe excel isn't the best tool to be using here.  But here is the problem, I dont know anything about Access and VBA connections to Autocad.  ADO vs. DAO, what does that mean?  Which should I use, and why?  And then, I have questions about how I should access my data, and if it can even be done.  Plus, how do I get a report out of Access?

These are the things I am turning to you guys for some guidance.  Im lost.....
Everyone has a photographic memory, Some just don't have film.
They say money can't buy happiness, but it can buy Bacon and that's a close second

Keith™

  • Villiage Idiot
  • Seagull
  • Posts: 16721
  • Superior Stupidity at its best
Re: Access NOOB - or what should I do?
« Reply #1 on: January 30, 2007, 09:06:44 AM »
I have had no success with programming access outside of the DB ... also keep in mind that if you create an Access DB to store your info, all users will have to have the version of access that you have, else they won't be able to open the DB (unless it is a newer version.. then if they change only one thing, you won't be able to open it)

Personally I recommend using excel as the files are compatable over many versions or use SQL ... and then it will be readily accessable via a web interface if desired, and can be accessed and modified with php, java, perl and a whole host of other programming languages.
Proud provider of opinion and arrogance since November 22, 2003 at 09:35:31 am
CadJockey Militia Field Marshal

CmdrDuh

  • Automatic Duh Generator
  • King Gator
  • Posts: 4039
Re: Access NOOB - or what should I do?
« Reply #2 on: January 30, 2007, 09:37:15 AM »
Thanks Keith!  How do I use SQL?  Sorry to be blunt, but I know nothing. :-D :-D  I just need a few pointers to get started.  Can I import a csv file into SQL? What do I edit a SQL file in?  Can I EASILY connect SQL to Autocad, and does it have choices like ADO/DAO or anything I need like that.

thanks
Everyone has a photographic memory, Some just don't have film.
They say money can't buy happiness, but it can buy Bacon and that's a close second

CmdrDuh

  • Automatic Duh Generator
  • King Gator
  • Posts: 4039
Re: Access NOOB - or what should I do?
« Reply #3 on: January 30, 2007, 09:39:15 AM »
Personally I recommend using excel
I too was thinking this, but when it quit working for no reason, and started working again with no changes to the formula, but having to create a new file, I have fears of coruption from Autocad-Excel connection.
Everyone has a photographic memory, Some just don't have film.
They say money can't buy happiness, but it can buy Bacon and that's a close second

Keith™

  • Villiage Idiot
  • Seagull
  • Posts: 16721
  • Superior Stupidity at its best
Re: Access NOOB - or what should I do?
« Reply #4 on: January 30, 2007, 09:46:37 AM »
Personally I recommend using excel
I too was thinking this, but when it quit working for no reason, and started working again with no changes to the formula, but having to create a new file, I have fears of coruption from Autocad-Excel connection.
The main problem with excel is that writing values to cells becomes tricky if you don't set the cell active ... for example .. you can tell VBA to put a value in cell B:21, and it may work flawlessly, but occasionally it will throw a wobbly .. the best way to resolve that situation is to set the cell active before trying to put a value in it.

As far as SQL is concerned, I am no DB programmer per se' I have always used an existing DB and simply used the methods exposed to interact with it. Perhaps a kind soul of a DB programmer will chime in with some help.
Proud provider of opinion and arrogance since November 22, 2003 at 09:35:31 am
CadJockey Militia Field Marshal

Tuoni

  • Gator
  • Posts: 3031
  • A programmer in this life, once again!
Re: Access NOOB - or what should I do?
« Reply #5 on: January 30, 2007, 09:47:33 AM »
I am using ADO to an excel spreadsheet for my current project at work.

In Tools -> References, add "ActiveX Data Objects 2.8 Library"

For querying your spreadsheet:

Code: [Select]
   Dim ExcelSpreadsheetADODB As ADODB.Connection
    Set ExcelSpreadsheetADODB = New ADODB.Connection
    Dim RecordSet As New ADODB.Recordset

    'Build the connection to the spreadsheet
    With ExcelSpreadsheetADODB
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .ConnectionString = "Data Source=" & ExcelFilename & ";Extended Properties=Excel 8.0;"
        .Open
    End With

    RecordSet.Open "SELECT * FROM [sheet1$] WHERE <column name>='" & variable & "'", _
                           ExcelSpreadsheetADODB, adOpenKeyset, adLockOptimistic

ADO is a notoriously "dark" artform... but I'm here to help if you need it... I think I've just about got it cracked (as much as is possible on the tat floating around on the internet)

hendie

  • Guest
Re: Access NOOB - or what should I do?
« Reply #6 on: January 30, 2007, 09:50:27 AM »
I've used Acad with Access for years and never had any problems with it. I also use Excel but much less. I find that it's slower with Excel and there have certainly been issues with Excel and Acad in the past.
I also have more options and more control in Access.
For me, if I was doing BoM's etc I woudl head down the Access route but that's just my preference

CmdrDuh

  • Automatic Duh Generator
  • King Gator
  • Posts: 4039
Re: Access NOOB - or what should I do?
« Reply #7 on: January 30, 2007, 09:52:42 AM »
Access was recommended here at work, but I have no access(no pun intended) to anyone that can help me.  I am relying heavily on you guys to steer me in the right direction.
Everyone has a photographic memory, Some just don't have film.
They say money can't buy happiness, but it can buy Bacon and that's a close second

Tuoni

  • Gator
  • Posts: 3031
  • A programmer in this life, once again!
Re: Access NOOB - or what should I do?
« Reply #8 on: January 30, 2007, 10:07:48 AM »
Well, my understanding is that with ADO, so long as you give it the right driver, you are able to connect to anything.

So.

www.connectionstrings.com should give you the correct connection string for connecting to access.

A simple select statement is "SELECT (what you want to select, * is everything) FROM (table name here) WHERE (condition 1) AND/OR (condition 2)"

You would file this in a recordset, as I showed in the last post (sorry about the brevity of that, I got called away half way through) and then you can filter the recordset (recordset.filter = "(conditions)") then recordset.movefirst will take you to the first record fulfilling all of your statements
« Last Edit: January 30, 2007, 10:09:38 AM by Tuoni »

Chuck Gabriel

  • Guest
Re: Access NOOB - or what should I do?
« Reply #9 on: January 30, 2007, 11:07:19 AM »
...
www.connectionstrings.com should give you the correct connection string for connecting to access.
...

I can see how that link might come in very handy.  Thanks.

Tuoni

  • Gator
  • Posts: 3031
  • A programmer in this life, once again!
Re: Access NOOB - or what should I do?
« Reply #10 on: January 30, 2007, 11:11:33 AM »
You're welcome.  Another good way to do it is to download MZtools (freeware!!!) which (amongst many other useful tools) has an ADO connection string builder... (unfortunately I discovered the website and MZtool's feature too late >.<)

I've been using MZtools since VB4 and, although not quite as powerful under VBA, it is a flippin' useful tool... even if only for reviewing your code :)

hendie

  • Guest
Re: Access NOOB - or what should I do?
« Reply #11 on: January 30, 2007, 11:17:34 AM »
http://www.vba-programmer.com/ is also a very useful site, not just for Access but for all things vba
I'm always stealing code from there

Guest

  • Guest
Re: Access NOOB - or what should I do?
« Reply #12 on: January 30, 2007, 01:36:22 PM »
<thinking out loud>
What if you write everything to a comma delimited file, then you can open it in either Access or Excel and format/calculate as you wish?
</thinking out loud>

CmdrDuh

  • Automatic Duh Generator
  • King Gator
  • Posts: 4039
Re: Access NOOB - or what should I do?
« Reply #13 on: January 30, 2007, 02:26:51 PM »
Thats kinda what Im thinking at this point.  I talked with some people here, and Some are saying SQL, which requires a server, others are saying stay in excel, while a fwe say access would work fine.  My thought is comma delimited file, then I can import into excel and format, or what would be really trick, bring it into Word through a template, and have a nice pretty document that gets filled out.  Does anyone know if that can be done?
Everyone has a photographic memory, Some just don't have film.
They say money can't buy happiness, but it can buy Bacon and that's a close second

Fatty

  • Guest
Re: Access NOOB - or what should I do?
« Reply #14 on: January 30, 2007, 06:10:40 PM »
Hi Commandor,
it's me again
I think you can't to find the better place
about Access than this one:

http://vbaexpress.com/forum/forumdisplay.php?f=16

IMHO

~'J'~

Glenn R

  • Water Moccasin
  • Posts: 1932
  • What idiot child of married cousins wrote this?!
Re: Access NOOB - or what should I do?
« Reply #15 on: January 30, 2007, 07:01:39 PM »
Duh,

I've started using SQL Server 2005 Express (the free one) and it's fantastic. By default, it doesn't allow network connections, however if you download and install SQL Server Management Studio Express, it's a front end to allow configuration of the dbase as well as user access and accounts. Essentially the main difference between express and the full biccy is that express will not address a dbase greater than 4gb and it will only use 1 CPU in a multiple CPU machine. I've found the dbases I'm creating with severall hundred thousand entries is nowhere near the limits.

Have a search also on Microsoft for their webcasts - there is an excellent starter series there on using SQL Express and VB.NET and C# and I'm sure you could use VBA with OLEDB to get access to Express if you really wanted.

Hope this helps.

Cheers,
Glenn.
Me

Tuoni

  • Gator
  • Posts: 3031
  • A programmer in this life, once again!
Re: Access NOOB - or what should I do?
« Reply #16 on: January 31, 2007, 04:17:12 AM »
With ADO you can address an excel spreadsheet as though it was a database, using SQL.

You can output to a word document using VB(a) by adding the reference to MS Word and then using ObjWord.selection.typetext?

Kheilmann

  • Guest
Re: Access NOOB - or what should I do?
« Reply #17 on: February 02, 2007, 01:32:34 PM »

A great source for Access and SQL help is www.utteraccess.com