Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #11 (permalink)  
Old July 17th, 2007, 10:08 AM
Friend of Wrox
 
Join Date: Mar 2007
Location: Hampshire, United Kingdom.
Posts: 432
Thanks: 0
Thanked 1 Time in 1 Post
Default

Hi JP,

I am assuming you have created an Access Application Instance Yes?
e.g.
Code:
Dim accApp
Set accApp = CreateObject("Access.Application")
accApp.DoCmd.SetWarnings = False
'Continue....
Give that a whirl and let us know!

Regards,
Rob

PS: Forgot to say, make sure you turn them back on as soon as you are done to be safe!!

<center>"Nothing can stop the man with the right mental attitude from achieving his goal;
nothing on earth can help the man with the wrong mental attitude".

Thomas Jefferson</center>
  #12 (permalink)  
Old July 17th, 2007, 01:10 PM
Authorized User
 
Join Date: Jul 2007
Location: Winston-Salem, nc, USA.
Posts: 32
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks, Rob!
             This gets me a little further down the road, as I'm not getting the "Need object" msg, but, the warnings still appear, exactly as though I'd never even tried to suppress them.... Message says something like "The changes you requested to the table were not successful because they would create duplicate values....." well, that's fine - Just reject the duplicates without making such a fuss about it, please! Does it make a difference what version of Access I'm using? It's Access 2003, if it matters....

Programming's a lot like kicking a dead whale down the beach...... You CAN make progress, but it's mighty slow.....





JP
  #13 (permalink)  
Old July 17th, 2007, 01:14 PM
Friend of Wrox
 
Join Date: Mar 2007
Location: Hampshire, United Kingdom.
Posts: 432
Thanks: 0
Thanked 1 Time in 1 Post
Default

The message box being shown, is it an ERROR message, or just an Access prompt??

If its an Error message, just handle it with nothing.

If not, then I'm confused!

Can you post your (relevant) code?

Rob

<center>"Nothing can stop the man with the right mental attitude from achieving his goal;
nothing on earth can help the man with the wrong mental attitude".

Thomas Jefferson</center>
  #14 (permalink)  
Old July 17th, 2007, 01:36 PM
Authorized User
 
Join Date: Jul 2007
Location: Winston-Salem, nc, USA.
Posts: 32
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I'll try to transcribe the whole msgbox content here:

Title is "Windows Script Host"

Script:(Pathname/scriptname).vbs
Line: 33
Char: 6
Error: Convert:
        The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again. From Microsoft JET Database Engine.
  Record Failed.
Code: 80004005
Source: (null)

Here's the VBScript code:


Dim path, fso, cnv, prj, numf, accApp
path = "c:\inetpub\ftproot"
numf = 0
Set cnv = CreateObject( "ConverterX.ConverterX.1" ) '<----- Create TextConverter object
Set fso = CreateObject("Scripting.FileSystemObject") '<----- Create File System object
Set accApp = CreateObject("Access.Application")
accApp.DoCmd.SetWarnings False

prj = "c:\SmartSys1.converterx"
cnv.OpenProject( prj ) '<----- Load the TextConverter Project
cnv.append = false '<----- Set the Append property to false

numf = numf + ScanFolders(fso.GetFolder( path )) '<----- Main Scanning procedure call

MsgBox "Files Converted: " & numf
accApp.DoCmd.SetWarnings True

'---------------
Function ScanFolders( folder ) '<----- Recursive folder scanning procedure
Dim sfs, sf, fls, nf, src
Set sfs = folder.SubFolders
For Each sf in sfs: nf = nf + ScanFolders( sf ): Next
Set fls = folder.Files
For Each fl in fls
    fn = fl.name

    If LCase(fso.GetExtensionName(fn)) = "csv" Then
        src = folder.Path & "\" & fn
        MsgBox src
        accApp.DoCmd.SetWarnings False
        cnv.LoadTextFile src, false '<----- Load Input Text File
        cnv.append = true '<----- Set the Append property to true for the following

conversions
        cnv.Convert()

    End If
Next
ScanFolders = nf
End Function


Thanks again for your help!!!!




JP
  #15 (permalink)  
Old July 17th, 2007, 03:24 PM
Authorized User
 
Join Date: Jul 2007
Location: Winston-Salem, nc, USA.
Posts: 32
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Oh, and one other question:
     What did you mean by "handle it with nothing"? What I'm trying to achieve is an "under the covers" process that can run noiselessly every couple of hours, with zero human intervention....

Thanks again!
                  Joe

JP
  #16 (permalink)  
Old July 17th, 2007, 03:33 PM
Friend of Wrox
 
Join Date: Mar 2007
Location: Hampshire, United Kingdom.
Posts: 432
Thanks: 0
Thanked 1 Time in 1 Post
Default

JP

Heres the code you posted, with alterations highlighted.
Code:
On Error Resume Next    'Enable Error Handling

Dim path, fso, cnv, prj, numf, accApp
path = "c:\inetpub\ftproot"
numf = 0
Set cnv = CreateObject( "ConverterX.ConverterX.1" )     '<----- Create TextConverter object
Set fso = CreateObject("Scripting.FileSystemObject")    '<----- Create File System object
Set accApp = CreateObject("Access.Application")
accApp.DoCmd.SetWarnings  False

prj = "c:\SmartSys1.converterx"
cnv.OpenProject( prj )                                  '<----- Load the TextConverter Project
cnv.append = false                                      '<----- Set the Append property to false
'I assume the error occurs when running the above line
If Err.Number = 80004005 Then Err.Clear 'Ignore Duplicate Values Error

numf = numf + ScanFolders(fso.GetFolder( path ))         '<----- Main Scanning procedure call

MsgBox  "Files Converted: " & numf
accApp.DoCmd.SetWarnings  True

'---------------
Function ScanFolders( folder )           '<----- Recursive folder scanning procedure
Dim sfs, sf, fls, nf, src
Set sfs = folder.SubFolders
For Each sf in sfs: nf = nf + ScanFolders( sf ): Next
Set fls = folder.Files
For Each fl in fls
    fn = fl.name

    If LCase(fso.GetExtensionName(fn)) = "csv" Then
        src = folder.Path & "\" & fn
        MsgBox src
        accApp.DoCmd.SetWarnings  False
        cnv.LoadTextFile src, false     '<----- Load Input Text File
        cnv.append = true               '<----- Set the Append property to true for the following 

conversions
        cnv.Convert()

    End If
Next
ScanFolders = nf
End Function
That should work, but it has been a while since I VBScripted
stuff, so I apologise if not, but let us know what happens.

Regards,
Rob

<center>"Nothing can stop the man with the right mental attitude from achieving his goal;
nothing on earth can help the man with the wrong mental attitude".

Thomas Jefferson</center>
  #17 (permalink)  
Old July 18th, 2007, 03:11 PM
Authorized User
 
Join Date: Jul 2007
Location: Winston-Salem, nc, USA.
Posts: 32
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks, Rob!!
                That's essentially what I was needing, except that the error dialog was issued after the "cnv.convert()" function call.

  Placing the "If err.number" test below the cnv.convert call has indeed shut the li'l bugger up, and all runs smoothly AND quietly!! I'm a happy camper!

 Thanks so much for your advice/assistance!

       Joe

JP
  #18 (permalink)  
Old July 19th, 2007, 02:12 AM
Friend of Wrox
 
Join Date: Mar 2007
Location: Hampshire, United Kingdom.
Posts: 432
Thanks: 0
Thanked 1 Time in 1 Post
Default

Joe,

Pleasure, glad I could be of help!

Good luck with the rest of your work, and please ask if you need anything else!

Rob

<center>"Nothing can stop the man with the right mental attitude from achieving his goal;
nothing on earth can help the man with the wrong mental attitude".

Thomas Jefferson</center>
  #19 (permalink)  
Old July 19th, 2007, 01:08 PM
Authorized User
 
Join Date: Jul 2007
Location: Winston-Salem, nc, USA.
Posts: 32
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Weeelll... All is not as rosy as I'd thought/hoped earlier....

Here's the snag:
                   If I have more than one file to feed through the "cnv.convert()" process, and the first one generates the error, the second and subsequent files are not processed at all.... sigh.

Is this controllable via that "On Error" statement, or is there something fundamentally wrong with the structure of the program itself?

        I'll keep hammering at it, but here's what it looks like at the moment:

'On Error Resume Next 'Enable Error Handling (commented out for now, to see the problem)
Dim path, fso, cnv, prj, numf
path = "c:\inetpub\ftproot"
prj = "c:\SmartSys1.converterx"
numf = 0
Set cnv = CreateObject( "ConverterX.ConverterX.1" ) '<----- Create TextConverter object
Set fso = CreateObject("Scripting.FileSystemObject") '<----- Create File System object
cnv.OpenProject( prj ) '<----- Load the TextConverter Project
cnv.append = false '<----- Set the Append property to false
numf = numf + ScanFolders(fso.GetFolder( path )) '<----- Main Scanning procedure call
Function ScanFolders( folder ) '<----- Recursive folder scanning procedure
Dim sfs, sf, fls, nf, src
Set sfs = folder.SubFolders
For Each sf in sfs: nf = nf + ScanFolders( sf ): Next
Set fls = folder.Files
For Each fl in fls
    fn = fl.name
    If LCase(fso.GetExtensionName(fn)) = "csv" Then
        src = folder.Path & "\" & fn
        MsgBox src
        cnv.LoadTextFile src, false '<----- Load Input Text File
        cnv.append = true '<----- Set the Append property to true for the following conversions
        cnv.Convert()
        If Err.Number = 80004005 Then
           Err.Clear 'Ignore Duplicate Values Error
        End If
    End If
Next
ScanFolders = nf
End Function



JP
  #20 (permalink)  
Old July 19th, 2007, 03:12 PM
Authorized User
 
Join Date: Jul 2007
Location: Winston-Salem, nc, USA.
Posts: 32
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Further investigation reveals that I don't even get control back to the VBScript from the cnv.Convert call if this error occurs. Looks like the simplest way to handle this is to remove the primary key from the table, and weed out the dups after the fact... I'm betting that'll be an easier task to handle, anyway... yes? Two fields (Date/Time stamp and machine name) will uniquely identify each record, and any that are identical to any others can be discarded....

   Does this sound more like a VBA task, or perhaps a macro?

    Joe

JP




Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel dialog boxes problem cunninb Excel VBA 3 November 24th, 2004 03:59 AM
Find and Replace Dialog Boxes PC User Access 0 July 6th, 2004 07:43 PM
pesky code kelvin Visual C++ 1 July 2nd, 2004 03:07 AM
no dialog boxes will appear amber Crystal Reports 1 May 18th, 2004 11:32 PM
SQL , List Boxes/Menu Boxes, DB's Ginzu3 Classic ASP Databases 1 June 30th, 2003 04:07 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.