Welcome to MapForums!

Register, sign in, or use Facebook Connect above to join in and participate in the forum.

When you are signed in, this message, the ads in this row, and the red-underlined link ads all go away.

Subscribe to receive our newsletter.
Subscribe Unsubscribe
Results 1 to 10 of 10

Advice Please... Access Code works but could be improved

This is a discussion on Advice Please... Access Code works but could be improved within the MapPoint Desktop Discussion forums, part of the Map Forums category; Hi all Looking for some help with this problem. I have a database with five tables. The master table has ...

  1. #1
    I.T. is offline Junior Member White Belt
    Join Date
    Mar 2005
    Posts
    9

    Advice Please... Access Code works but could be improved

    Hi all

    Looking for some help with this problem.

    I have a database with five tables. The master table has all of the reps from different companies. There are a further four tables with the customers from these different companies (makes sense to the database (honest!! )).

    I have all of the Reps and customers locations from their eastings and northings (UK). I designed a simple search form where the company, area and rep codes can be selected which displays the location of the Rep on a map. Really easy so far.

    Things went a bit off when I then decided to try and plot all of the customers for that particular rep on the same map. Should have been easy enough as I already had the company and rep codes from the Access Form and it should just have been a simple case of passing these variables to a query in the database.

    This simple problem kept throwing up "Unable to connect to Database" errors (even though it was the self same database that I had open that I was trying to query). The only way that I could do it was to create a temporary table, set and plot the dataset from that then delete the temporary table.

    The following code works but sometimes throws up the same error ("Unable to connect...").

    Code:
    Dim db As Database
    Dim custcount As Long
    Dim stTbl, stCoTbl, stTempTbl, stCrit, stSQL, stQry As String
        
    Set db = CurrentDb()
    
        stTbl = "CustTbl" & Me.Co & " OS"
        stCrit = "[Area] = " & Me.Area & " AND [Rep_ID] = " & Me.Rep 
        custcount = DCount("[Cust_ID]", stTbl, stCrit) 
    
        Me.OSGrid = DLookup("[Easting]", "LocTbl", "([Co] = [Forms]![SrchFrm]![Co]) AND ([Area] = [Forms]![SrchFrm]![Area]) AND ([Rep] = [Forms]![SrchFrm]![Rep])") & " " & DLookup("[Northing]", "LocTbl", "([Co] = [Forms]![SrchFrm]![Co]) AND ([Area] = [Forms]![SrchFrm]![Area]) AND ([Rep] = [Forms]![SrchFrm]![Rep])")
        Me.OSGrid.Visible = True
        Me.Customers = custcount & " Customers"
        Me.Customers.Visible = True
    
        Dim oMpApp As New MapPoint.Application
        Dim oDS As MapPoint.DataSet
        Dim RepLoc, RepPin As Object
    
        stCoTbl = Me.Co & "-" & Me.Area & "-" & Me.Rep
        stSQL = "SELECT [Easting] & "" "" & [Northing] AS [OS Grid Reference] INTO [" & stCoTbl & "]" _
            & "FROM [" & stTbl & "] " _
            & "WHERE ((([" & stTbl & "].Area)=" & Me.Area & ") AND (([" & stTbl & "].Rep_ID)=" & Me.Rep & "));" 
        DoCmd.RunSQL stSQL
        Set RepLoc = oMpApp.ActiveMap.LocationFromOSGridReference(OSGrid, 25)
        Set RepPin = oMpApp.ActiveMap.AddPushpin(RepLoc)
        With oMpApp.ActiveMap.DataSets
            stTempTbl = db.Name & "!" & stCoTbl
            Set oDS = .ImportData(stTempTbl, , geoCountryUnitedKingdom, , geoImportAccessTable)
            oDS.ZoomTo
        End With
        RepPin.Symbol = 60
        oDS.Symbol = 20
        RepPin.highlight = True
        oMpApp.Visible = True
        oMpApp.UserControl = True
        DoCmd.DeleteObject acTable, stCoTbl
    
    End Sub
    I would be VERY grateful for any suggestions to improve this as I feel it is a rather clunky and slow solution to what would appear to be a fairly simple problem of passing variables to an Access Query.

    Thanks in advance

    Iain T.

  2. #2
    WonderMonkey is offline Junior Member Yellow Belt
    Join Date
    Apr 2006
    Posts
    22
    Would it be possible to zip up a subset of the data along with your project? I'll take a look at it if you can. If you don't feel comfortable with that, it's understandable.

  3. #3
    I.T. is offline Junior Member White Belt
    Join Date
    Mar 2005
    Posts
    9
    Thanks for the reply...

    Will prepare an anonymised dataset - save the boss going mental about data protection

    I'll get back to you as soon as poss..

    Cheers

    I.T.

  4. #4
    looney_doc is offline Junior Member White Belt
    Join Date
    Jan 2008
    Posts
    6

    Re: Advice Please... Access Code works but could be improved

    Hello I.T.,

    I've read your post looking for a solution of the same problem. Unfortunately the discussion did not progress further. Did you find a clue?

  5. #5
    Wilfried is offline Senior Member Black Belt
    Join Date
    Nov 2004
    Location
    Belgium
    Posts
    2,433

    Re: Advice Please... Access Code works but could be improved

    Hi,

    As far as I can see quick on the original post is that he try to connect mappoint to some dataset. but mappoint has his own datasets and every dataset is different, so you cannot just connect to something else. you have to populate it looping through the records.

  6. #6
    looney_doc is offline Junior Member White Belt
    Join Date
    Jan 2008
    Posts
    6

    Re: Advice Please... Access Code works but could be improved

    Hello Wilfried,

    thanx for your reply.

    My problem with using ImportData from an Access Table from the currently used database, is the error "unable to connect to database".

    I thought it to be a problem before even trying to import Data into the MapPoint Datasets.
    Perhabs there's a problem with my declaration part??

    I posted my code just below, perhabs it is easy to see.

    Public Function Georef()

    Dim KH As Object
    Dim db As Database
    Dim cn As ADODB.Connection
    Dim objApp As New MapPoint.Application
    Dim objMap As MapPoint.Map
    Dim objDataSet As MapPoint.DataSet
    Dim objPat As MapPoint.DataSet 'Patientenverteilung
    Dim objShp As MapPoint.Shape
    Dim objLoc As MapPoint.Location
    Dim objAdj As MapPoint.Adjustments
    Dim objField As MapPoint.Field
    Dim Adresse, DSM As String
    Dim objRecordset As MapPoint.Recordset
    Dim objSym As MapPoint.Symbol
    Dim ImportArray(1, 1)
    Dim Pfad As String

    On Error GoTo GeorefError

    'reading Data for georeference
    Set db = CurrentDb
    Set KH = db.OpenRecordset("KH-Daten", dbOpenDynaset)
    KH.MoveFirst
    objApp.Visible = True
    objApp.UserControl = True
    Set objMap = objApp.ActiveMap
    Adresse = KH![KH-Strasse] & "+" & Str(KH![KH-PLZ]) & "+" & KH![KH-Ort]
    Set objLoc = objMap.FindAddressResults(KH![KH-Strasse], KH![KH-Ort], , , Str(KH![KH-PLZ]), geoCountryGermany)(1)
    objApp.ActiveMap.AddPushpin objLoc, KH![KH-Name]
    objApp.ActiveMap.DataSets(1).Name = KH![KH-Name]
    objApp.ActiveMap.DataSets(1).Symbol = 298
    objApp.ActiveMap.DataSets.ZoomTo

    'initialising Importarray
    ImportArray(0, 0) = "PLZ"
    ImportArray(1, 0) = "Anzahl"
    ImportArray(1, 1) = geoFieldInformation

    'Set cn = New ADODB.Connection
    'cn.Open (CurrentProject.Connection)

    Pfad = db.Name & "\MAA_LS.mdb!maa_Patientenherkunft"
    Set objPat = objApp.ActiveMap.DataSets.ImportData(Pfad, ImportArray, geoCountryGermany, 0, geoImportAccessTable)


    objApp.ActiveMap.Saved = -1 'MapPoint fragt nicht nach speichern der Karte

    Exit Function

    GeorefError:
    MsgBox Err.Description, vbCritical, "Fehler"
    End Function
    Thanx for your help!

  7. #7
    Wilfried is offline Senior Member Black Belt
    Join Date
    Nov 2004
    Location
    Belgium
    Posts
    2,433

    Re: Advice Please... Access Code works but could be improved

    Hi,

    Code:
    ImportArray(0, 0) = "PLZ"
    ImportArray(1, 0) = "Anzahl"
    ImportArray(1, 1) = geoFieldInformation
    Should you not specify witch fields are latitude and longitude ?

    Code:
    Pfad = db.Name & "\MAA_LS.mdb!maa_Patientenherkunft"
    Set objPat = objApp.ActiveMap.DataSets.ImportData(Pfad, ImportArray, geoCountryGermany, 0, geoImportAccessTable)
    Did you also try geoImportAccessQuery ?

    Sorry don't have more idea's at the moment...

  8. #8
    looney_doc is offline Junior Member White Belt
    Join Date
    Jan 2008
    Posts
    6

    Re: Advice Please... Access Code works but could be improved

    Hello Wilfried,

    thanx for your reply. I'm a real rookie in programming MapPoint with VBA and therefore happy about every advice.

    As far as I understood it, latitude and longitude are only two items of the dataset object you may "organise" in an importarray - just as geofieldinformation is one.

    For I want to import data from an access table, I did't try the query method. The program application flow makes the query run seperately, which should remain this way.

    Wouldn't this cause an error concerning the data types instead of unability of connecting to the database?

    Thank you, this far....

  9. #9
    Wilfried is offline Senior Member Black Belt
    Join Date
    Nov 2004
    Location
    Belgium
    Posts
    2,433

    Re: Advice Please... Access Code works but could be improved

    Hi,

    I think you have to specify which field is latitude and which one is longitude. Something like this (C#):

    Code:
    object[,] fieldSpecifications = null;
    fieldSpecifications = new object[2, 2];
    //Specify what fields are geographic and what fields are not
    fieldSpecifications[0, 0] = "Lat";
    fieldSpecifications[0, 1] = MapPoint.GeoFieldType.geoFieldLatitude;
    fieldSpecifications[1, 0] = "Lon";
    fieldSpecifications[1, 1] = MapPoint.GeoFieldType.geoFieldLongitude;
    dataSet = MP.ActiveMap.DataSets.ImportData(fileName, 
    				       fieldSpecifications, 
    				       MapPoint.GeoCountry.geoCountryDefault,
    				       MapPoint.GeoDelimiter.geoDelimiterSemicolon, 0);

  10. #10
    looney_doc is offline Junior Member White Belt
    Join Date
    Jan 2008
    Posts
    6

    Re: Advice Please... Access Code works but could be improved

    Hi Wilfried,

    just to complete this question...I found the mistake.

    I mixed the DAO and ADODB connections to the database and so the data could not be imported.

    You don't have to record the latitude and longitude of a dataset, but may also just record postal code or adress.

    If anyone has the same problem, here is my code that works:
    Public Function Georef()
    Dim cn As ADODB.Connection
    Dim KH As ADODB.Recordset
    Dim objApp As New MapPoint.Application
    Dim objMap As MapPoint.Map
    Dim objDataSet As MapPoint.DataSet
    Dim objPat As MapPoint.DataSet 'Patientenverteilung
    Dim objShp As MapPoint.Shape
    Dim objLoc As MapPoint.Location
    Dim objAdj As MapPoint.Adjustments
    Dim objField As MapPoint.Field
    Dim Adresse, DSM As String
    Dim objRecordset As MapPoint.Recordset
    Dim objSym As MapPoint.Symbol
    Dim ImportArray(1, 1)
    Dim Pfad As String
    On Error GoTo GeorefError

    'reading Data for georeference

    Set KH = New ADODB.Recordset
    Set cn = CurrentProject.AccessConnection
    KH.Open "select * from [KH-Daten]", cn
    KH.MoveFirst
    objApp.Visible = True
    objApp.UserControl = True
    Set objMap = objApp.ActiveMap
    Adresse = KH![KH-Strasse] & "+" & Str(KH![KH-PLZ]) & "+" & KH![KH-Ort]
    Set objLoc = objMap.FindAddressResults(KH![KH-Strasse], KH![KH-Ort], , , Str(KH![KH-PLZ]), geoCountryGermany)(1)
    objApp.ActiveMap.AddPushpin objLoc, KH![KH-Name]
    objApp.ActiveMap.DataSets(1).Name = KH![KH-Name]
    objApp.ActiveMap.DataSets(1).Symbol = 298
    objApp.ActiveMap.DataSets.ZoomTo

    'initialising Importarray
    ImportArray(0, 0) = "PLZ"
    ImportArray(1, 0) = "Anzahl"
    ImportArray(1, 1) = geoFieldInformation

    Pfad = CurrentProject.Path & "\MAA_LS.mdb!maa_Patientenherkunft"
    Set objPat = objApp.ActiveMap.DataSets.ImportData(Pfad, ImportArray, geoCountryGermany, 0, geoImportAccessTable)
    objApp.ActiveMap.Saved = -1 'MapPoint fragt nicht nach speichern der Karte

    Exit Function

    GeorefError:
    MsgBox Err.Description, vbCritical, "Fehler"
    End Function
    Wilfried, thanx again for your help. See you again in MP2K.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Pushpin software advice please
    By nsundby in forum MapPoint Desktop Discussion
    Replies: 6
    Last Post: 06-30-2005, 04:38 AM
  2. New to MapPoint, Advice on this solution . . .
    By Anonymous in forum MapPoint Desktop Discussion
    Replies: 0
    Last Post: 02-04-2004, 07:49 AM
  3. Could someone explain how item() works on shapes collection?
    By spideybud in forum MapPoint Desktop Discussion
    Replies: 0
    Last Post: 12-15-2003, 02:02 PM
  4. to import data of one access file into another using vb code
    By bushi_khan in forum MapPoint Desktop Discussion
    Replies: 0
    Last Post: 10-14-2003, 02:12 AM
  5. VBA Access 2002 Code Help Needed
    By rkehn in forum MapPoint Desktop Discussion
    Replies: 0
    Last Post: 01-26-2003, 01:23 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91