Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 513
  • Last Modified:

Run SQL Append Query

I appears I need to basically take a hammer and pound some information into a table because of an error code I am getting due to something imbedded in Windows 7 and XP code that doesn't make any sense.  I am getting a run-time error 3051 saying I don't have rights to a table on computers which are running Windows 7.  If a computer is running XP it doesnt get the message and runs the append query without any problem.  If there is a more graceful way of doing it I am fine with it but if not just making the updates work is all I am wanting to do at this point so the admin team can do their job.  Here is the code.

(BTW- Originally, I was just running the Tbl_SDrive_FileNames (This table is a linked table to a file on the network) to append information and then tried to copy the data into a table so the query would have the data in a table that wasn't linked and that didn't work either.)

Any suggestions would be helpful.  I had a user login to a Windows 7 machine and they got the error and then an XP machine and the code ran so this is a Windows issue so just getting a hammer out and getting it to work is all I need at this point.  

NOTE:  Tbl_SDrive_FieldNames is a linked table to a file on the network.
Private Sub Frame85_Click()
  Select Case Frame85

    Case 1

      Dim i As Integer
      Dim sqlfield As String



      Call Shell("\\jassrv03\jas-fp$\Documents\filename.bat", vbminimize)



      DoCmd.RunSQL "Delete * from tbl_sdrive_temptable"

      sqlfield = "INSERT INTO Tbl_SDrive_TempTable ( Field1 )SELECT Tbl_SDrive_FileNames.Field1 FROM Tbl_SDrive_FileNames"

      DoCmd.SetWarnings False ' this didn't help but I left the code in as a reference


>>>>> This is where the error code happens regardless of the method used when referencing the Tbl_SDrive_FieldNames file on the network>>>>>
      DoCmd.RunSQL sqlfield


      DoCmd.OpenQuery "Qry_NotAttachedFiles", acViewNormal, acEdit
      DoCmd.Close acQuery, "Qry_Notattachedfiles", acSaveYes
      If IsNull(DLookup("[Document on S Drive]", "No Document Date on File Name (Rename before continuing)")) = False Then

        DoCmd.OpenQuery "No Document Date on File Name (Rename before continuing)", acViewNormal, acReadOnly

      Else

        MsgBox "Import was successful."
      End If

Open in new window

0
mtrussell
Asked:
mtrussell
  • 5
  • 3
  • 2
  • +1
1 Solution
 
Nick67Commented:
Ok,
Just for giggles, create a new share, and not a hidden one.
In a Win7 environment hidden shares (\\servername\hidden$) are now something that I think are reserved for admins only.
It is DEFINITELY worth a shot
0
 
mtrussellAuthor Commented:
Is there any other way of popping the information out of the bat file into an access table?  I am not the admin administrator and changing the architecture of the network is a battle which won't be won in my lifetime.  IF there are any other options, I'd prefer to go this route before marching off to that front.
0
 
aikimarkCommented:
What is in the bat file?

One thing I noticed about the code snippet is that the code doesn't wait for the bat file to finish before continuing.  I don't know if this might matter to your problem.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
mtrussellAuthor Commented:
Thanks for the comment.  The bat file a bunch of file names in a txt format.  

Also, I deleted a line of code in the above that is delaying the running of the rest of the code by 10 seconds to give the bat file time to run (it was a function and didn't want to leave the code there since it wasn't visible in the question).  It is being done though.

I do think this is an admin issue where I can see the file but can't copy or paste the data.  I didnt know if there was a way via vba just to override all this and make it run since as I said on an XP machine we can do it.
0
 
aikimarkCommented:
Unless these file names have actions associated with their file type (.cmd, .bat, .exe) a "list of files" isn't going to do anything but cause error messages during the .bat file's execution.
0
 
mtrussellAuthor Commented:
They are just .doc, .xls, etc names.  nothing else except for the one .bat file.  The issue is Access being able to copy new file names in a folder on the network over to a table in Access.  The code is breaking at this point.
0
 
aikimarkCommented:
If you type the names of .doc and .xls files at a command prompt, you might cause the files to be opened by the associated applications (Word and Excel).  That could interfere with file processing.
0
 
mtrussellAuthor Commented:
nothing is being opened.  It isn't the command.  it is a windows 7 issue.   I'm not sure how to work around the way W7 is not allowing the information in a linked table to be copied to another table in Access.
0
 
GRayLCommented:
What happens if you change line 23 from:

23:      DoCmd.RunSQL sqlfield

to:

23:      CurrentDB.Execute sqlfield

Of course with the latter you do not need to turn off the Warnings
0
 
Nick67Commented:
<I had a user login to a Windows 7 machine and they got the error and then an XP machine and the code ran so this is a Windows issue >
Agreed.
Login as a Domain Admin on Windows 7 and run it.
Does it run?
If it does, then you have a subtle permissions issue.
0
 
mtrussellAuthor Commented:
Thanks!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now