Solved

VB6 update table in Access db

Posted on 2006-10-23
3
1,331 Views
Last Modified: 2012-08-14
I have tried everything I found on the forum so far but none of them do the trick (most likely due to me not the answers).

I have a VB6 program that another person made years ago.  I have added and re-vamped to the best of my ability but now I want to call an Access update query in the source mdb and am getting this error.  

I'm getting the error
Run-time error'91'
Object variable or Wtih block variable not set.

I believe that because I have the recordset open in my VB program, it is recognizing that I have the table open once already and doesn't want me to make changes but I can manually run the agents if other people are in the program but I am only in Access.

One of the postings had a link to this microsoft KB and I think it's on the right track but can't figure out how to adapt it to my program.
http://support.microsoft.com/kb/287485/en-us

Also, on a side not, I'm just beginning to play around in SQL and was thinking that maybe I could run update query using/adapting the SQL behind the Access macros so that I don't have to open the mdb but haven't gotten that far yet so I'm definitely open to anything that goes in that direction as well.

I'm not certain what information would be most usefull so here is some of the program in case it helps:

DataEnvironment1 (Datalink properties: Share Deny None)
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=H:\ROCDATA\ROCAccounting.mdb;Persist Security Info=False

ADODC is
dattechinfo
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=H:\ROCDATA\ROCAccounting.mdb;Persist Security Info=False

Here is my current code behind my button on the form
 Dim db_app As Access.Application
 
 Const dbPathName = "H:\ROCDATA\ROCAccounting.mdb"
 Const acPreview = 2

 Dim Acc As Object, db As Object

 Set Acc = CreateObject("Access.Application")
 Set db = Acc.Application.DBEngine.Workspaces(0).OpenDatabase(dbPathName, 0, False)
 Acc.OpenCurrentDatabase dbPathName, False
 Acc.Visible = True

 'The next line is where it is failing.  It also fails if I make an Access macro and call that from here.
    db_app.DoCmd.OpenQuery "qryAMaketblHPWflags"
    db_app.DoCmd.OpenQuery "qryMaketblHPWUpdates"
    db_app.DoCmd.OpenQuery "qryAppendNewHPWJobsTotblTechInfo"
    db_app.DoCmd.OpenQuery "qryUpdateHPWtoTechInfo"
    db_app.DoCmd.OpenForm "MessageComplete"

 Set db = Nothing
 Set Acc = Nothing
0
Comment
Question by:VanessaTamora
  • 2
3 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 17788008
Most Access programmers I know in this situation would execute action queries using either ADO or DAO, and not the Access Application object.

Which in your case would be...

Set db = Acc.Application.DBEngine.Workspaces(0).OpenDatabase(dbPathName, 0, False)
with db
   .Execute "qryAMaketblHPWflags"
   .Execute "qryMaketblHPWUpdates"
   .Execute "qryAppendNewHPWJobsTotblTechInfo"
   .Execute "qryUpdateHPWtoTechInfo"
   .Execute "MessageComplete"
End With
0
 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 total points
ID: 17788018
{slight correction}

Set db = Acc.Application.DBEngine.Workspaces(0).OpenDatabase(dbPathName, 0, False)
with db
   .Execute "qryAMaketblHPWflags"
   .Execute "qryMaketblHPWUpdates"
   .Execute "qryAppendNewHPWJobsTotblTechInfo"
   .Execute "qryUpdateHPWtoTechInfo"
End With

db_app.DoCmd.OpenForm "MessageComplete"  '<-- Not sure about this one, you'll have to test it
0
 
LVL 7

Expert Comment

by:yotamsher
ID: 17791092
the problem in your code, seems to be that you don't set the db_app object anywhere
it seems that you mixed two peaces of code, so either use:
      Acc.DoCmd.OpenQuery "qryAMaketblHPWflags"

or put somewhere the line:
      Set db_app = CreateObject("Access.Application")

I think this will do.
   Yotam
     
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

This article is meant to give a basic understanding of how to use R Sweave as a way to merge LaTeX and R code seamlessly into one presentable document.
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

920 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now