Edit .mdb Database from Excel

[ssVBAdev]

Seems I'm more of a questioner lately rather then a helper... But, I've got another question.

I have an .mdb database that I am able to open from excel (OpenDatabase), extract the data I need and enter into an excel spreadsheet in a format that is easy to understand.

Fine.

But I want to be able to "correct" or edit some of that data from my "user friendly" GUI I've setup in excel and send that back to the .mdb database.

I wouldn't be adding any records, simply overwriting some of the data of some of the records.

Something like this pseudo code:

If .cells(Row, 1).Value = Record Then
   If .cells(Row, 2).Value <> Data1 Then Overwrite Data
   If .cells(Row, 3).Value <> Data2 Then Overwrite Data
   .
   .
   .

End If

Following?? Am I explaining myself correctly??

It's the "Overwrite Data" part that I am not sure about. The rest of the code around it is a breeze, but I just don't know how to see if Data in the Record matches my Excel data and if it does not, then how do I overwrite it?

********************
What's the best way to get the answers you need?? See FAQ222-2244 for details!

 

[SkipVought]

Hi ssVBAdev,

What you are describing is an UPDATE query.

Set a reference in VB Editor to Microsoft ActiveX Data Objects x.y Library. (the most recent)

You are going to have to set an ADO recordset object and a connect object to you database. Your recordset object needs to be updateable, probably a Table-Type recordset.

Look at the Help examples. Code and example and see how it works.

You will have to have your table cursor positioned on the correct record to do the Update. Key values cannot be Updated.

Good luck! It will be a learning expertince. Poat back with your questions.





Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[ssVBAdev]

HMM...

Doesn't sound as simple as I thought. Getting the data from the database was simple. I guess I thought it'd be simple to "put it back" also.

And Yes, I'm sure it'll be a learning experience. I'll try to post back if I find anything relatively soon.

Anyone else have any thought's that might lead me in the right direction...

********************
What's the best way to get the answers you need?? See FAQ222-2244 for details!

 

[SkipVought]

If I were at my other computer, I'd have some examples at hand.

Here I don't.

But I'll check it out tonite and post an example.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[ssVBAdev]

Hey!! That'd be great! I appreciate that Skip. Won't get to see 'em until tomorrow though... day's almost done and my finger is hovering precariously above the Shut Down button... (manner of speaking of course).

********************
What's the best way to get the answers you need?? See FAQ222-2244 for details!

 

[SkipVought]

Thrust that digit it forward with firm resolve!

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[ssVBAdev]

OH BABY!!!

I've got it working and man-o-man does it ever work slick!! Well, that is to say that I have the backbone working; I'll need to "dress it up" a little.

A little caveat here... I'm not a database expert by any means and, in fact, this is my first try at a "real", usable database app... but here goes...

I used "transaction" (BeginTrans, CommitTrans, Rollback) to transfer the data from Excel to the Database.

Make sure you have Microsoft DAO 3.6 Object Library in your references.

I simply go through each record of the database,

Do While Not .EOF

and

.MoveNext

test if it meets a certain critera, finds the data in the excel spreadsheet that it needs and use the .edit and .update methods to transfer the data.

.Edit
!quan = Worksheets("Assembly").Cells(Row, Col).Value
.Update

WOO HOO!!

Thanks for the help though Skip!

********************
What's the best way to get the answers you need?? See FAQ222-2244 for details!

 

[SkipVought]

Ain't it a rush to discover stuff that works!

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[ssVBAdev]

I guess my exuberance showed through huh

********************
What's the best way to get the answers you need?? See FAQ222-2244 for details!