Solved

Run SQL Append Query

Posted on 2011-09-20
12
495 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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Suggested Solutions

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
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…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

770 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