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...").
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.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
Thanks in advance