Store data in DB

[MeiR_ct]

Hi all.
This is a new post, which is very similar to [and kind of cross-posted from] http://www.vbaexpress.com/forum/showthread.php?p=225763 , since I have not introduced my request properly there, and perhaps that's why people avoided answering.
I have marked the old post as "Solved", because of this one.

I have this kind of sheet:


I need a loop to run on the sheet, and create a section in the DB for every "property ID" (column P).

First of all, the columns positions may change from time to time, so at the start, there will be several "find" commands, that store the columns in variables:
[vba]BusNameCol = rows(1).find(what:="Business Name").column
OwnerIDCol = rows(1).find(what:="Owner ID").column
PropIDCol = rows(1).find(what:="Property ID").column
OwnerNameCol = rows(1).find(what:="Owner Name").column
OwnerAddrCol = rows(1).find(what:="Owner Address").column
SignIDCol = rows(1).find(what:="Sign ID").column
SignContentCol = rows(1).find(what:="Sign Content").column
SignWidthCol = rows(1).find(what:="Width (cm)").column
SignHeightCol = rows(1).find(what:="Height (cm)").column
SignAreaCol = rows(1).find(what:="Rounded Area").column
SignStreetCol = rows(1).find(what:="Sign Address - Street").column
SignHouseCol = rows(1).find(what:="Sign Address - House").column
SignLocCol = rows(1).find(what:="Sign Location").column[/vba]


As you can see, the lines that match one property ID, have the same owner and property data, but a different signs data.

So if the property ID doesn't exist in the DB, the macro will create it, and add the one-time data. And if it exists, it'll add to it the signs data.
Here's a kind of textual algorithm:

put 2 in variable "x"
while "x" smaller than last line
    put the value from line "x" and column "PropIDCol" in variable "CurrentProp"
    if a "Property ID" record named "CurrentProp" doesn't exists in file "Data.db"
        create a "Property ID" record named as "CurrentProp" in file "Data.db"
        add to "CurrentProp" record, a sub-record "BusinessName" with value from line "x" and column "BusNameCol"
        add to "CurrentProp" record, a sub-record "OwnerID" with value from line "x" and column "OwnerIDCol"
        add to "CurrentProp" record, a sub-record "OwnerName" with value from line "x" and column "OwnerNameCol"
        add to "CurrentProp" record, a sub-record "OwnerAddress" with value from line "x" and column "OwnerAddrCol"

    put the value from line "x" and column "SignIDCol" in variable "CurrentSign"
    create inside "CurrentProp" record, a sub-record "Sign ID"  named as "CurrentSign"
    add to "CurrentSign" sub-record, a sub-sub-record "Content" with value from line "x" and column "SignContentCol"
    add to "CurrentSign" sub-record, a sub-sub-record "Width" with value from line "x" and column "SignWidthCol"
    add to "CurrentSign" sub-record, a sub-sub-record "Height" with value from line "x" and column "SignHeightCol"
    add to "CurrentSign" sub-record, a sub-sub-record "Area" with value from line "x" and column "SignAreaCol"
    add to "CurrentSign" sub-record, a sub-sub-record "AddressStreet" with value from line "x" and column "SignStreetCol"
    add to "CurrentSign" sub-record, a sub-sub-record "AddressHouse" with value from line "x" and column "SignHouseCol"
    add to "CurrentSign" sub-record, a sub-sub-record "Location" with value from line "x" and column "SignLocCol"
    
    put  (x + 1) in variable "x"
end of while loop

I'm not familiar with the DB syntax, so here is an output example in XML format which I wanted first, but Kenneth suggested DB.
The DB pattern should be the same:

 <Property id="166143000003">

    <BusinessName>Paz</BusinessName>
    <OwnerID>510216054</OwnerID >
    <PropertyID>166143000003</PropertyID>
    <OwnerName>Paz Company Ltd.</OwnerName>
    <OwnerAddress>P.O.B. 222 City</OwnerAddress>
    <Sign id="2010004701">

        <Content>Tasty Yellow</Content>
        <Width>250</Width>
        <Height>230</Height>
        <Area>6</Area>
        <AddressStreet>rd. 434</AddressStreet>
        <AddressHouse>21</AddressHouse>
        <Location>Display Window</Location>

    </Sign>
    <Sign id="2010004801">

        …
        …

    </Sign>
    etc. etc.

</Property>
<Property id="36100001550">

    …
    …
    …

</Property>
etc.
etc.

* Notice: I don't need the DB for any special purpose like SQL server and so, I just need a way to store my data, so I can update records later and do actions on them, like creating report files.
* If it's possible, I'd like to have also a button that does the job for a single (the current selceted) line.
* Please notice also line 12 in the picture, which has no property ID. I'm not sure yet what to do in this case. Maybe to prompt a messages that tells about it?


Thanks a lot in advance!
Meir.

[MeiR_ct]

[This is Kenneth's reply from the old closed thread:]

ADO methods similar to what Deyken posted can be used. You will need some modifications though like the connection string.

The first thing to do is decide how you will create the dbf file. If you have Access, I would do it manually first. If not, you can do it by programming. For the programming method, see the post by Jan Karel Pieterse, http://vbaexpress.com/forum/showthread.php?t=24883.

Once the dbf is created, we can easily fill it with the data.

If you don't have Access, be sure to get the MDAC files from Microsoft which are free.

The next step is to insert the rows as records into the dbf file. You can then run some SQL code to remove duplicates or use some more specific ADO code to iterate the rows and check for a matching with the same ID and not insert it.

Here is one simple method for poking Excel data into a dbf file.
[vba]Sub demo()
Dim objRS As Object, nwindPath As String
Set objRS = CreateObject("ADODB.Recordset")
nwindPath = ThisWorkbook.Path & "\nwind.mdb"

Dim r As Range
[a1] = "LastName"
[b1] = "FirstName"
[a2] = "Hobson"
[b2] = "Kenneth"
Set r = [a1:b2]
r.Name = "MyRange"

objRS.Open "INSERT INTO Employees SELECT * FROM [MyRange] IN '" & ThisWorkbook.FullName & "' 'Excel 8.0;'", _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & nwindPath

Set objRS = Nothing
End Sub[/vba] I probably won't have time to give more specific help until this weekend. These ideas should give you something to play with though.

For some more specific ADO code, see: http://www.excelguru.ca/node/18

Notice that these code examples use early binding so you need to set the reference to the Microsoft Data Activex Connection library object.

Hello again, Kenneth!
I entered to your links and read a little on net.

The first thing I saw is that there're several db file types, like "accdb, "fdb", "mdb", etc.
I admit it confused me a bit
Can you please explain what's the difference between them, so I can determine what suits my needs the most?
All that I need is a db format which can contain sub-records.

And secondly, about accessing the db for checking a record's existence.
I saw two possible ways to find out that a record wasn't found [perhaps there are more]:
1. To query the db with "select" command, and to check if connection gets to EOF.
2. To use "count" function with the record name params, and to check if it returns 0.
What's the better? And what's the exact syntax I should use?

I have no knowledge in vba, and obviously have no idea about combining excel vba and access dbs
Can you or someone please "translate" my algorithm to vba? I think it'll do the work, unless someone sees some logic problem.

Thanks in advance!

[deyken]

Shalom, Meir

I trust you are doing well! I now read through your entire post and would like to make the following suggestions, based on your questions:

1. Your Database requirements echo a resounding necessity for MS Access. Any version really, but in more detail:
1.1 *.mdb is for MS Access 2003 and earlier file types
1.2 *.accdb is for MS Access 2007 and up.
-> I suggest you get MS Access of the same MS Office version that your MS Excel version is. This simplifies any compatibility issues in the future

2. Sub-records, linked to main records is what nerds like me in the Database programming world call: Master-Detail relationships. MS Access is perfect for this.
2.1 We will create a MS Access Database file
2.2 We will add a DB Table for each record-type that you require
2.3 We will then set up a "Relationship" between these tables

3. SQL is very basic for the functions you need to perform. VBA isn't. The algorithm you propose in your post, however, is very straight forward and shouldn't pose too many complications.
3.1 We will then create VBA Algorythms to collect the information from the Cells (Columns & Rows) on your existing Excel Worksheet(s)
3.2 We will then create SQL Statements to take the info from 3.1 and put them (line by line) into your new MS Access Database tables
3.3 We will create VBA "Functions" for each task that could be linked into a Button for each task

4. I only know the "long" way of programming things and Pro's like Kenneth might know programmtical short cuts. I always find the long way to be more robust. I have been programming (in Delphi mostly) Database Applications for many years, so SQL is what I do...

My next post will have the code and content. In the meantime, could you send the Excel sheet you mention? This would make testing the code much easier on my side.

[MeiR_ct]

deyken, loved your "Shalom"!!! Heh

Thanks for your detailed reply.
Don't worry, I have MS Access installed with the whole Office package.
The whole thing is done from Excel beacuse that's where we handle the data for years in our company.

I know a little about Access and structure of relationships. I just hope that the whole method of writing to Access DB, and later also reading from it, can be easily done through Excel vba without probs.

And about the sample file, sorry for forgetting it!
I did attach it to the previous thread, and again now.

Thanks again =]

[deyken]

Hi Meir,

I will attach the MS Access Database File to this post, once I figure out how to do it . Please have a look at it? If you have MS Access on your machine, then please access this DB File and have a look at the tables I created and the relationship between these tables.

Once you have familiarised yourself with the Database, then send me the Excel spreadsheet with all the data you want entered into this database. I can then work on the best VBA code to put the data into your DB...

[MeiR_ct]

I will attach the MS Access Database File to this post, once I figure out how to do it

If it's about the file type, archive the .mdb file inside ZIP. It's an allowed file type to upload here.
And if you don't see the "Manage Attachments" button, that's because you should simply press "Go Advanced" first.

My original and complete spreadsheet is at work's PC, and I won't be there until Sunday. Anyway, it's in Hebrew language, which probably not supported on your machine.

I've attached a 19 lines sample file here in my previous reply. That's a copy-paste that I translated to English from the complete sheet, which contains thousands of lines.
If you manage to do the job with these 5 businesses, I believe it'll be effective. =]

Let me know if you need details or if there are updates.
Thanks.

[MeiR_ct]

By the way, my Office version is 2002 SP3.
I believe it's relevant.

[MeiR_ct]

Hi Deyken and Kenneth.
I made a cross-post of my request at: http://www.mrexcel.com/forum/showthread.php?p=2464789
I guess I should inform... ^^

Thanks a lot for your guidelines! You've helped a lot.
Let me know if there's any new

[MeiR_ct]

Perhaps someone can just write for me the loop structure in vba?
He can put some text instead of the DB manipulating code.
I'll try to replace it later, when someone with DB knowledge helps me.

Thanks

[Kenneth Hobs]

I already wrote the loop for you. However, with the dbf method that I posted, you don't need that.

If you want to get the ball rolling, create a dbf with the fields that you want to export and post that. It is then very easy to insert the excel data into the dbf.