Appending an access table with linked excel document 2

[JohnnyG23]

Ok, what I have is a linked excel document and a table in access I want to append the contents of the table to. From what I have read it would be easiest to use queries to do this. I am not very fluent in access so I am not sure if queries are the best thing to do. So far all I have code wise is this.

Private Sub new_employees_Click()
On Error GoTo Err_new_employees_Click
    Dim qryTemp As QueryDef, tblTemp As TableDef, rstemp As Recordset, strSQL As String, RecCnt As Integer
    
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "ADD new employees"
    'DoCmd.OpenQuery "UpdateCURRENT"
    'DoCmd.OpenQuery "CURRENT Without Matching TechID"
    'DoCmd.OpenQuery "NewEmpTechID"
    DoCmd.OpenQuery "Update preferred_name"
    'DoCmd.OpenQuery "UpdateTechIDs"
    '''Line below sends email'''
    'SendMsg
    DoCmd.SetWarnings True
    MsgBox "Complete", vbOKOnly

    
Exit_new_employees_Click:
    Exit Sub

Err_new_employees_Click:
    MsgBox Err.Description
    Resume Exit_new_employees_Click
    
End Sub

This code was taken from a previous version of the program they used a long time ago. The reason most of it is commented out is because those queries are from the older program and I have not created them, not really sure how. The ones that aren't commented out my predecessor had created. If that code looks good would I need to create all those queries or can it done through an easier method or will it need to be coded? Also, the table is called CURRENT and the linked table is called Employees. Thanks.

 

[MazeWorX]

What you want is either append >>> add new data to the table or update >>> update existing records in the table. Yes queries are an easy way to do this the code above would be attached to a command button and would run the queries. You would have to determine what you want to do append or update then create or edit existing queries to obtain your desired results.

HTH

MaZeWorX

Remember amateurs built the ark - professionals built the Titanic

[flush]

 

[JohnnyG23]

Alright, what I'm wanting to do is append the data. I am thinking I should be able to append all this data with only one query. The person before me made an append query, link below, and I am wondering if they set it up right. I am wanting to append the CURRENT table with the linked excel Employees table. Just looking at it it looks right to me, but whenever I execute it it doesn't append anything. I clicked the button to execute and even click the query itself in the list and neither did anything. I am just wondering if I need to start with a fresh query or if this one looks right and maybe I'm just not implementing it correctly? Thanks.

http://img34.imageshack.us/my.php?image=12712492.jpg

Private Sub new_employees_Click()
On Error GoTo Err_new_employees_Click
    Dim qryTemp As QueryDef, tblTemp As TableDef, rstemp As Recordset, strSQL As String, RecCnt As Integer
    
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "ADD new employees"
    DoCmd.SetWarnings True
    MsgBox "Complete", vbOKOnly
    
Exit_new_employees_Click:
    Exit Sub

Err_new_employees_Click:
    MsgBox Err.Description
    Resume Exit_new_employees_Click
    
End Sub

 

[PaulDalyTT]

I do something similar in my own app, but I use the following docmd options to run the query...

DoCmd.OpenQuery "APPEND_FINAL_01-TLF_TO_WEEKLY_FINAL", acViewNormal, acEdit

Mabye give these a shot?


I cant access imageshack from work so cant see your query, but if the query itself does nothing when you double click it, then that would suggest a problem with the query and not your VB code.

Is there definatley data in the linked sheet?? (is there something to append?)

 

[PHV]

Why not posting the SQL code of the query ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JohnnyG23]

Alright, I gave up on the pre-made query and created one myself. It works, the only problem is that because there are little changes, like a preferred name may be Carol Links in the CURRENT table and Carol S Links in the linked Employees document, so what happened was because of all the little changes my table doubled in size almost.

What I'm trying to do now is create an update query to avoid that problem. But, when I run the update query it sits for a few minutes the brings back an error that says Cannot update '(expression)'; field not updateable. I tried removing all but one of the fields to see if there was just one field with this problem, but I tried three different fields and they all had this error so I think it's an overall problem, not just one fields problem. I don't think my CURRENT table is locked because I was able to add those 5000+ new entries to it so I'm not sure what the problem is at this point. Anyone have any ideas here? Thanks.
I'll go ahead and post the SQL code for the Update query just in case.

UPDATE Employees, [CURRENT] SET Employees.EMPLNAME = [CURRENT].[employee_name], Employees.LASTNAME = [CURRENT].[last_name], Employees.FIRSTNAME = [CURRENT].[First_name], Employees.TECHID = [CURRENT].[techid], Employees.SUFFIX = [CURRENT].[suffix], Employees.PREFIX = [CURRENT].[prefix], Employees.COA = [CURRENT].[coa], Employees.STATUS = [CURRENT].[status], Employees.SPCLSTAT = [CURRENT].[SPCLSTAT], Employees.BCAT = [CURRENT].[BCAT], Employees.FLSA_CODE = [CURRENT].[FLSA], Employees.PART_FULL = [CURRENT].[part_full], Employees.[POSITION] = [CURRENT].[position], Employees.POS_TITLE = [CURRENT].[position_title], Employees.FTE = [CURRENT].[fte], Employees.HOME_DEPT = [CURRENT].[home_dept], Employees.UNITNAME = [CURRENT].[unitname], Employees.PREFERRED_NAME = [CURRENT].[preffered_name], Employees.WORK_ADDRESS = [CURRENT].[work_addr], Employees.WORK_CITY = [CURRENT].[work_city], Employees.WORK_STATE = [CURRENT].[work_state], Employees.WORK_ZIP = [CURRENT].[work_zip], Employees.SEX = [CURRENT].[gender], Employees.CURRENT_HIRE_DATE = [CURRENT].[current_hire_date], Employees.NTRPCLS_CODE = [CURRENT].[ntrplcs_code], Employees.DATE_REFRESHED = [CURRENT].[date_refreshed], Employees.SortCode = [CURRENT].[sortcode], Employees.[School\Division] = [CURRENT].[school\division], Employees.[Requires Postage] = [CURRENT].[requires postage];

 

[PHV]

What are the primary keys of each table ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JohnnyG23]

I would say TechID would be the primary key. It currently isn't set as the primary key so is that something that I should do?

 

[PHV]

So, replace this:
UPDATE Employees, [CURRENT] SET
with this:
UPDATE Employees INNER JOIN [CURRENT] ON Employees.TechID = [CURRENT].TechID SET

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JohnnyG23]

Ok, got that done and now it brings back that error right after running the query.

 

[PHV]

You can't update a linked excel table with a query (since access XP I believe).

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JohnnyG23]

I'm only trying to update an Access table from a linked Excel document.
Sorry if I haven't been clear enough. This part of the program has been stressing me out.

 

[MazeWorX]

I believe the problem may lie with "[CURRENT].[school\division]" Access maybe trying to execute it as an expression. Try not to use characters like “\”, "-", "+" which may be recognized as mathematics operators.

HTH M

Remember amateurs built the ark - professionals built the Titanic

[flush]

 

[JohnnyG23]

Ok, tried out what you said and I'm still getting that error. Whenever I swap to Datasheet view it has quite a bit of info in there. It looks like everything in there should be updated so I think it's going to update correctly I just don't understand why it gives me that error. I am using "_" instead of a space in some fields, should I get rid of that and try it? Thanks.

 

[MazeWorX]

sorry Johnny I'm a night shift guy who needs to sleep. I will revisit it at work tonight when i can think a little more clearly

Remember amateurs built the ark - professionals built the Titanic

[flush]

 

[BJCProgrammer]

Just got on the forum. I have done some vba programming by bringing up different office applications. You could write vba code in excel. Bring ACCESS up when you bring up the spreedsheet. When you see a field of data change bring up update the table with vba code then shut ACCESS down when you exit. Kind of like live updating as the data is changing in excel. Don't have to have link table in access. Just a thought.. would be fun programming!

 

[JohnnyG23]

Ok, something very wierd happens with Access query editor. I was working in the Design view and it has the base table as Employees and then updates the CURRENT table, which is what I wanted. So, I looked at the SQL view and it has Employees table set to equal the CURRENT table, which PHV says won't work so I think that's why I got that error message. So I guess the design view and SQL code were opposite of each other for some reason. I don't know why it's that way but I reversed the SQL code so CURRENT was equal to Employees and now it works just fine, thus far. I only updated one field but I'm sure the rest will work just fine. Thanks for all the help though, MazeWorX, as well as everyone else.
Problem solved for now. =)