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


        MsgBox "Import was successful."
      End If

Open in new window

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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.
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.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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.
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.
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.
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.
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.
What happens if you change line 23 from:

23:      DoCmd.RunSQL sqlfield


23:      CurrentDB.Execute sqlfield

Of course with the latter you do not need to turn off the Warnings
<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 >
Login as a Domain Admin on Windows 7 and run it.
Does it run?
If it does, then you have a subtle permissions issue.

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mtrussellAuthor Commented:
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.