Pulling from Multiple Tables in VB

[Canadiangal]

I am new to VB and I am trying to pull data from 4 tables. This is what I have but Access keeps telling me I am using a reserved word. So really two questions, what is my reserved word, and am I joining the tables correctly in VB



Table HR
Table Employees
Table DepartmentTitle
Table DepartmentData

HR is joined to Employees by EmployeeID
DepartmentTitle is joined to DepartmentData by DepartmentID
Employees is joined to DeparmentData by EmployeeID

[Code]

DoCmd.RunSQL "SELECT DISTINCTROW " & _
"DepartmentData.Department, DepartmentTitles.Title, Employees.LastName, " & _
"Employees.FirstName, Employees.EmployeeID, " & _
"Hr.[Original Hire Date] " & _
"INTO WrkTable FROM HR INNER JOIN Employees INNER JOIN ON Employees.EmployeeID" &_
"= HR.EmployeeID" &_
"DepartmentData INNER JOIN DepartmentTitles INNER JOIN ON DepartmentData.DepartmentID" &_
"= DepartmentTitles.DepartmentID" &_
"ORDER BY DepartmentData.Department, DepartmentTitle.Title, Employees.LastName, Employees.FirstName;" DoCmd.RunSQL "ALTER TABLE WrkTable ADD COLUMN Chosen Logical;"
DoCmd.RunSQL "ALTER TABLE WrkTable ADD COLUMN CourseNumber Long;"
DoCmd.RunSQL "ALTER TABLE WrkTable ADD COLUMN Attended Logical;"
DoCmd.RunSQL "ALTER TABLE WrkTable ADD COLUMN DateAttended Date;"
DoCmd.RunSQL "UPDATE WrkTable " & _
"SET WrkTable.CourseNumber = Forms!EdClasses!CourseNumber.Value;"

DoCmd.OpenForm "EdEnroll"
DoCmd.SetWarnings True

End Sub

[orange]

It is unclear WHAT exactly you are trying to do. Often to assist readers it is best to describe your "issue"/opportunity in plain English -- just as you would tell someone who doesn't know you or your application or Access. In addition, if you have tables and relationships, please post a jpg of same to show the readers the structures you are working with. It helps provide context and aids in communication.

I don't understand why you are using code to alter table structure. I recommend using a naming convention that uses only alpha and numeric characters. If you allow spaces in field and object names you will run in to syntax errors (sooner or later).

[Canadiangal]

The code alters the work table and updates the classes that the employees have been enrolled in. This table is overwritten each time an enrollment is run. Then it is updated in their main records. I certain can do it using a make table, but I am unsure of how to complete this with VB. You are correct, I should have used a better naming convention. I am simply trying to duplicate a database using Access 95.
My questions are: based on the relationships, am I pulling from the tables correctly?
Based on my code above, what reserved word am I using?

[orange]

There is no need to ALTER your table via code that I can see, but still don't have much info.

You can delete records and keep the existing structure.

Your picture doesn't show all fields in tables. You mention classes but I see no table for classes??

You also have mentioned VB -- do you mean vba? or are you accessing this database thru VB.net???

Can you post a copy of your database -- remove confidential info?

I don't see the reserved word, but you didn't show all the code related to the procedure.
It's hard to comment on the relationships until we understand the "business" involved.

There is a tutorial here that may put many of these concepts together
http://www.rogersaccesslibrary.com/T...lationship.zip

[Canadiangal]

Basically what I am asking is how do you change SQL to VBA. In other words, if I direct my code to run a query that does a Make table, then it works, however; I am trying to write it all in VBA. I am unsure of how to write it.
I only showed the tables that are pertinent to this part of the code. The class code comes in later and not in this section.

My SQL is
SELECT Employees.FirstName, Employees.LastName, DepartmentData.Title, Department.Department, HR.[Original Hire Date], Employees.EmployeeID INTO WrkTable FROM (Employees INNER JOIN (Department INNER JOIN DepartmentData ON Department.departmentID = DepartmentData.[Department ID]) ON Employees.EmployeeID = DepartmentData.EmployeeID) INNER JOIN HR ON Employees.EmployeeID = HR.EmployeeID
ORDER BY Department.Department;

[orange]

Here's a tutorial (s) that will show SQL and vba
http://www.fontstuff.com/access/acctut15.htm

[Canadiangal]

Here's a tutorial (s) that will show SQL and vba
http://www.fontstuff.com/access/acctut15.htm

Thank you, that was very, very, very helpful!

I do have a question tho, that even after reading SQL and vba that still have me baffled. I am looking at another persons code. I did not write it, just trying to understand it.

What I am completely baffled is how it works, each and every time.

Private Sub Command29_Click()
Dim rsc As Recordset
Dim ClassNmb As Long
   'DoCmd.Beep
   DoCmd.SetWarnings False
   DoCmd.RunSQL "SELECT DISTINCTROW " & _
   "Employees.Department, Employees.Title, Employees.LastName, " & _
   "Employees.FirstName, Employees.[Employee Number], Employees.[Original Hire Date] " & _
   "INTO WrkTable FROM Employees " & _
   "ORDER BY Employees.Department, Employees.Title, Employees.LastName, Employees.FirstName;"
   DoCmd.RunSQL "ALTER TABLE WrkTable ADD COLUMN Chosen Logical;"
   DoCmd.RunSQL "ALTER TABLE WrkTable ADD COLUMN CourseNumber Long;"
   DoCmd.RunSQL "ALTER TABLE WrkTable ADD COLUMN Attended Logical;"
   DoCmd.RunSQL "ALTER TABLE WrkTable ADD COLUMN DateAttended Date;"
   DoCmd.RunSQL "UPDATE WrkTable " & _
                "SET WrkTable.CourseNumber = Forms!EdClasses!CourseNumber.Value;"
      
   DoCmd.OpenForm "EdEnroll"
   DoCmd.SetWarnings True
   
End Sub

After reading the tutorial, shouldn't this code fail the second time you run it since the four columns have already been added?

[orange]

Baffles me too.
Why Dim variables that you don't use?

Dim rsc As Recordset
Dim ClassNmb As Long

Why turn off any Warning/Error messages?

DoCmd.SetWarnings False

Why create a Table that you don't appear to use?
Why Alter a Table structure in code in a working/operational application?

There are so many issues that jump out of this code that you could work on and IMPROVE.

If you have any idea of what this procedure is trying to do in plain English, then you could work with the SQL/vba and re-write the code.
People here will help.
But to go through a tutorial(s), and still be baffled surprises me.

After reading the tutorial, shouldn't this code fail the second time you run it since the four columns have already been added?

May be it fails, but how would you know that since the warnings have been turned off.

Do yourself a favour - figure out from the application what clicking that button is suppose to do. Tell us in plain English and we'll help you with the sql/vba or whatever.

[Canadiangal]

I did figure it out. I'm not sure why they declared the variables, but they did. It does a make table. So the table gets over-written each time. The table doesn't originally include the four added columns, thus it works. Turned off the message and was prompted to over write. Mystery solved!

[orange]

Yes, the mystery is solved, but do you want to fix the code so you know what it's doing?
Where is the data(records) deleted from the table?
Is there any comments/documentation in the code?
Are you the "database" person and are you responsible for the database/application?

[Canadiangal]

Yes, the mystery is solved, but do you want to fix the code so you know what it's doing?
Where is the data(records) deleted from the table?
Is there any comments/documentation in the code?
Are you the "database" person and are you responsible for the database/application?

The data deleted from the table has been updated in the main records. There are absolutely no comments at all in this database. I am actually a professor in IT. More profiecient in building networks than writing any type of code.

[orange]

Professor where?

[Canadiangal]

Originally Vancouver Island, now in the US.