Join shapefile to MS Access attribute table using VBA

MapWinGIS
1

Hi

I have an MapWinGIS application in MS Access. I need to join a shapefile to an attribute data table that is in the same access database. Can I create a SQL Query for the attributes, and join that to the shapefile?

Thanks

Stu

2

me again . . .

I see from another post that the process is:

  • add fields to the shapefile
  • Copy access data into the fields

Is this correct?

Thanks

stu

3

Hello stu.

I can’t answer your original question. Regarding your second suggestion; this is certainly possible, and not unreasonable, keeping in mind that if the data is not static, you will have to update the shapefile periodically from the attribute tables. But algorithms can be set up to do this.

Regards,
Jerry.

4

Jerry

I created a program to refresh the data from MS Access each time the map is generated. It’s slow, but the data will always be correct.

Thanks again

Stu

5

Hi

Not really sure why you require the attribute data in the shape file, but if it is just for displaying the details for users if they click on a shape, then what I do is get the key value from the shape file and then you can run a query/report using this key value in a query against your access table. I use linked sql tables but it would be the same.

Regards

Colleen

6

Hi Colleen

I have a MS Access database that stores planned roadways maintenance by year. From this data, I want produce a map of proposed maintenance treatments, by road segment. I load this data into the shapefile, then use MapWinGIS shapefile Categories to generate the map.

Why did I do it this way?

I don’t know how to join a shapefile to an Access table in MapWinGIS, and be able to use the shapefile category function.

on a different topic . . .

Have you embedded MapWinGIS maps in MS Access reports? I’m having grief trying to get the map to redraw when the report goes into print preview.

Thanks for your comments

stu

7

Oh I see, then yes you do need the data in the shapefile (I think) - I am also very new to this. I don’t think you can join a shape file to another table in access but I may be wrong.

I only have the ocx in a form, I do however export the Map to a jpg (success = image.Save(ExpMapPath, True, MapWinGIS.ImageType.JPEG_FILE)) - maybe you could do this and put the jpg in the report. (If I understand correctly what you are doing)

Regards

Colleen

8

Looking at your original post you say that the shape file is in the same access database. I was not aware you could have shape fields in an access database. I am wondering if this is the same as what I have done in SQL. I set my data up in SQL with a table that was just the shape and I join this to another table of data and use this to load onto the Map (by using a view). However you can’t group if you have a shape field, so where I wanted to group data I would have to run a stored procedure to group all the data as required, write it to a temporary table, and then join that temporary table with the table that has a shape field in it using the unique key.

9

Hi Colleen

  1. Sorry for my lack of clarity. The shape file is not “in” the access database, I connect to it through MapWinGIS. But I do update the fields in the shapefile with MS Access. using:
l = sf_Lines.StartEditingShapes()
y = sf_Lines.EditCellValue(3, Lines, WORK_YEAR)

  1. I’m not sure if it is the same as you have done in SQL. If you are grouping the data to categorize it on the map, it is the same thing. I have to run a group query in access to load the categories for the map.

  2. My code to load the categories (I don’t know if this helps) the code loads data into three shapefiles for lines, points, and polygons.

    '---------------------------------------------------
    ' Load Treatments as shapefile Categories
    '---------------------------------------------------
Dim db2 As dao.Database
Dim rst_Shapefile_Treatments As dao.Recordset2

Set db2 = CurrentDb()

     sql_Shapefile_Treatments = "SELECT * FROM [QRY Map Shapefile Treatments];"
        
        
    Set rst_Shapefile_Treatments = db2.OpenRecordset(sql_Shapefile_Treatments)
        
        rst_Shapefile_Treatments.MoveLast
        rst_Shapefile_Treatments.MoveFirst

        
        For treat = 1 To rst_Shapefile_Treatments.RecordCount
        
                   T_GIS_TYPE = rst_Shapefile_Treatments.Fields("GIS_TYPE")
                    Treatment = rst_Shapefile_Treatments.Fields("TREATMENT")
                        T_Red = CInt(rst_Shapefile_Treatments.Fields("RED"))
                      T_Green = CInt(rst_Shapefile_Treatments.Fields("GREEN"))
                       T_blue = CInt(rst_Shapefile_Treatments.Fields("BLUE"))
        
        Select Case T_GIS_TYPE
        
                Case "Line"
                           sf_Lines.Categories.ClassificationField = 4
                      With sf_Lines.Categories.Add(Treatment)
                            .ValueType = cvExpression
                            .Expression = "[TREATMENT] = """ & Treatment & """"
                            .DrawingOptions.Visible = True
                            .DrawingOptions.LineColor = RGB(T_Red, T_Green, T_blue)
                            .DrawingOptions.FillColor = RGB(T_Red, T_Green, T_blue)
                            .DrawingOptions.FillBgColor = RGB(T_Red, T_Green, T_blue)
                            .DrawingOptions.LineWidth = 5
                     End With
                               
                Case "Point"
         
                        With sf_Points.Categories.Add(Treatment)
                                   .ValueType = cvExpression
                                   .Expression = "[TREATMENT] = """ & Treatment & """"
                                   .DrawingOptions.Visible = True
                                   .DrawingOptions.FillColor = RGB(T_Red, T_Green, T_blue)
                                   .DrawingOptions.PointSize = 20
                        End With
                           
                Case "Polygon"

                        With sf_Polygons.Categories.Add(Treatment)
                               .ValueType = cvExpression
                               .Expression = "[TREATMENT] = """ & Treatment & """"
                               .DrawingOptions.Visible = True
                               .DrawingOptions.FillColor = RGB(T_Red, T_Green, T_blue)
                               .DrawingOptions.LineWidth = 5
                        End With

        End Select

        rst_Shapefile_Treatments.MoveNext
        
        Next treat
        
        
   sf_Lines.Categories.ApplyExpressions
   sf_Points.Categories.ApplyExpressions
   sf_Polygons.Categories.ApplyExpressions
   
 Map0.Redraw
  1. Which produces the following map:

image
image503×581 54.4 KB

stu

10

Hi

OK, understand now - I think what you are doing is correct as I also could find no way to join the shape file to one of my SQL linked tables (or to an access table). Therefore I went outside of access and did all the joining in SQL so when I loaded the shape file it was from a view in SQL that was already joined to the shape (in an SQL table). Hence I did not need to edit the shape file and add data. However, if you are using an Access table I would think you have to add the fields to the shape file as you have done, I did try this originally and it was too slow. I have something very similar to you were I show sections of road that are different colours for the different rate of accidents on the road, but I did this via SQL view.