Solved

vb.NET application using AccessDB to run MAKETABLE query wont work on other machine. errors dont make sense. Possibly Access version difference issue?

Posted on 2013-01-16
2
331 Views
Last Modified: 2013-04-03
I know my heading doensnt make much sense, but im quite lost as to what is happening!

i have the following code:

Sub UpdateStatusReport()


    Try
        Dim oAccess As Access.Application
        oAccess = New Access.Application()

        oAccess.OpenCurrentDatabase("I:\GPS_Conversion\XY_Convert2.mdb", True)
        oAccess.DoCmd.SetWarnings(WarningsOn:=False)
        oAccess.DoCmd.OpenQuery("A1qryMt_FindMaxStatusReportID")
        oAccess.DoCmd.OpenQuery("A2qryUpd_ReduceMaxStatusReportID")
        oAccess.DoCmd.OpenQuery("A3qryMt_StatusReport")
        oAccess.DoCmd.SetWarnings(WarningsOn:=True)
        System.Runtime.InteropServices.Marshal.ReleaseComObject(oAccess)
        oAccess = Nothing
        Console.WriteLine("updated table with live info")
    Catch ex As Exception
        Dim ex1 = ex.ToString
        My.Computer.FileSystem.WriteAllText("C:\logfile.txt", ex1, True)
    End Try

End Sub

Open in new window


I import the following:

Imports System
Imports System.Data
Imports System.Data.OleDb
Imports System.Data.DataRowExtensions
Imports System.Data.SqlClient
Imports System.ComponentModel
Imports Microsoft.Office.Interop
Imports ADODB

Open in new window


the error i get from the above is:

System.Runtime.InteropServices.COMException (0x800A09C5): The OpenQuery action was canceled.
   at Microsoft.Office.Interop.Access.DoCmd.OpenQuery(Object QueryName, AcView View, AcOpenDataMode DataMode)
   at CurrentPositionHTMLCreate.Module1.UpdateStatusReport() in C:\IT3_New\Michael\CurrentPositionHTMLCreate\CurrentPositionHTMLCreate\Module1.vb:line 30System.Runtime.InteropServices.COMException (0x800A09C5): The OpenQuery action was canceled.
   at Microsoft.Office.Interop.Access.DoCmd.OpenQuery(Object QueryName, AcView View, AcOpenDataMode DataMode)
   at CurrentPositionHTMLCreate.Module1.UpdateStatusReport() in C:\IT3_New\Michael\CurrentPositionHTMLCreate\CurrentPositionHTMLCreate\Module1.vb:line 30System.Runtime.InteropServices.COMException (0x800A09C5): The OpenQuery action was canceled.
   at Microsoft.Office.Interop.Access.DoCmd.OpenQuery(Object QueryName, AcView View, AcOpenDataMode DataMode)
   at CurrentPositionHTMLCreate.Module1.UpdateStatusReport()System.Runtime.InteropServices.COMException (0x800A09C5): The OpenQuery action was canceled.
   at Microsoft.Office.Interop.Access.DoCmd.OpenQuery(Object QueryName, AcView View, AcOpenDataMode DataMode)
   at CurrentPositionHTMLCreate.Module1.UpdateStatusReport()System.Runtime.InteropServices.COMException (0x800A09C5): The OpenQuery action was canceled.
   at Microsoft.Office.Interop.Access.DoCmd.OpenQuery(Object QueryName, AcView View, AcOpenDataMode DataMode)
   at CurrentPositionHTMLCreate.Module1.UpdateStatusReport()

Open in new window


Its a small sub that calls a series of MAKETABLE queries in access.

On the computer that i wrote the program on it works fine, but when i use any other computer i get all sorts of issues. Ive narrowed it down by slowly breaking apart the code until i found what didn't work, from the whole application.

basically this updates a table in the DB from a DBO that is on a slq server. I cant get direct access to that server, i can only access it via the accessDB with these queries in it.

basically, ive come to the conclusion that its a version issue? it looks like i have office 2007 on this machine, and most other machines ive tried it on has 2000 or 2003. but i installed "Microsoft Access Database Engine 2010 Redistributable" on those machines thinking that it might be the issue, but it hasnt resolved my problem.

is there another way i can do this? is there some backward compatible code i can write that might make it workable in older versions of access? Im not really sure what im looking for here, but i need a way to be able to get the live data from the DBO's and update it into the database that i use for this application.

thanks,

Mike
0
Comment
Question by:AER
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 83

Assisted Solution

by:CodeCruiser
CodeCruiser earned 250 total points
ID: 38786152
Have you installed the Office Interop and ADODB on the machine?
0
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 250 total points
ID: 38798157
I don't use Office Interops much, but I believe you must have the correct "version" of that interop to use with different versions of Office (at least for the newer versions of 2007 and 2010). 2007/2010 use the new ACE format for databases, whereas earlier versions use JET.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

631 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question