Solved

Run SQL Append Query

Posted on 2011-09-20
12
500 Views
Last Modified: 2012-05-12
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
Comment
Question by:mtrussell
[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
  • 5
  • 3
  • 2
  • +1
12 Comments
 
LVL 26

Expert Comment

by:Nick67
ID: 36571232
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
 

Author Comment

by:mtrussell
ID: 36572442
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
 
LVL 45

Expert Comment

by:aikimark
ID: 36573286
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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 

Author Comment

by:mtrussell
ID: 36573371
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
 
LVL 45

Expert Comment

by:aikimark
ID: 36573437
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
 

Author Comment

by:mtrussell
ID: 36573682
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
 
LVL 45

Expert Comment

by:aikimark
ID: 36573846
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
 

Author Comment

by:mtrussell
ID: 36574013
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
 
LVL 44

Expert Comment

by:GRayL
ID: 36574830
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
 
LVL 26

Accepted Solution

by:
Nick67 earned 500 total points
ID: 36574853
<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
 

Author Closing Comment

by:mtrussell
ID: 37077927
Thanks!
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

752 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