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.

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.

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.

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?

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.

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!

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...

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....

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);

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.