Results 1 to 2 of 2
  1. #1
    Join Date
    May 2014
    Posts
    16

    Unanswered: Access Problem Reading SQL Server Database

    Front End: MS Access 2010
    Back End: SQL Server 2008

    I want to write in Access VBA code a routine that will add to the database an Employee table with a Skills table associated with it (one to many relationship). I have done this many times in Access but not in VBA. The add routine for Employee is complete. What I am having problems with is the Skills. For example to add the records:

    zSQL = _
    "INSERT INTO [dbo_Employee] " & _
    "(employee, position)" & _
    " VALUES(" & _
    "'" & Remployee & "'" & _
    ", '" & Rposition & "')"

    DoCmd.SetWarnings False
    DoCmd.RunSQL zSQL

    This adds the Employee. How do I add the Skills?

    SkillID int <-- primary key
    EmployeeID int <-- FK Skill to Employee
    Skill varchar(50)
    DateTaken datetime

    Any help will be appreciated.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    flaky design:-
    i reckon there shoudl be a table for employees
    a table for skills ONLY
    a table for employees skills (and intersection table which identifies what skills an employee has and anythign else pertinent to that association (eg date take, assessment and so on

    do an insert for each employee skill pairing

    presumably you'd have a combo box on your form which identifies the skills available, the suer selects which skill/skills apply then your writ ethe data.

    I wouldn't expect you to be writing to the skills table from the empoyeees form

    if you are being smart about the combo box, you could requery the row source for the combo box displaying only those skills which the employee doesn't already have (by joining the intersection table to the skills table.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •