Solved

Run SQL Append Query

Posted on 2011-09-20
12
496 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
  • 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

821 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