Need help writing info from one DB to another DB 2

[platypus71]

I've got a working function that obtains the values necessary for this within the following:

Set rst = mydb.OpenRecordset(sqlstr)

I know this works as the function works. Now I find out I need to write into a new table several of these values for tracking purposes.
Since this function will be used in multiple other functions, I am trying to keep it as generic as possible.

Here is what I have:

Function TrackingLog(UserID, CourseName, EventID, CourseID, Type)

Dim writelog As String

writelog = "INSERT INTO tblLog ( UserID, CourseName, EventID, CourseID, Type ) IN 'J:\Log.mdb'"

DoCmd.RunSQL writelog

End Function

Then, from the other function, I call this function with

Dim Y As Boolean
Y = TrackingLog(rst!UserID, rst!CourseName, rst!EventID, rst!CourseID, Type)

It claims I have a syntax error in my INSERT statement.

 

[belovedcej]

Have you tried adding the semi-colon at the end of your insert statement? I have on occasion found that to be an answer.

 

[PHV]

syntax error in my INSERT statement
Sure: no VALUES nor SELECT clause !

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Zion7]

as PHV said...

New table

strSQL = "SELECT * " & _
"INTO tblLog " & _
"IN 'J:\Log.mdb' " & _
"From tblLog"

New record

strSQL = "INSERT INTO tblLog( UserID, CourseName, EventID, CourseID, Type ) " & _
"IN 'J:\Log.mdb' " & _
"VALUES(" & rst!UserID & ",'" & _
rst!CourseName & "'," & _
rst!EventID & ", " & rst!CourseID & _
", '" & Type & "')"

 

[platypus71]

Either I wasn't clear or I just don't get it.

Here is the code I have:

Function OneDayReminder()

Dim Y As Boolean

Dim mydb As Database
Dim rst As Recordset
Dim sqlstr As String

Dim ReminderType As String
ReminderType = "One Day"
sqlstr = "SELECT ReconReport.EventID, Courses.CourseID, Courses.CourseName, ReconReport.StartDate, ReconReport.EndDate, ReconReport.Location, ReconReport.Time, Roster.[User ID] AS UserID, Roster.Status FROM (Courses INNER JOIN ReconReport ON Courses.CourseID = ReconReport.CourseID) INNER JOIN Roster ON ReconReport.EventID = Roster.EventID WHERE (((ReconReport.StartDate)>(Now()+1) And (ReconReport.StartDate)<(Now()+2)) AND ((Roster.Status) = 'Enrolled'));"
Set mydb = CurrentDb
Set rst = mydb.OpenRecordset(sqlstr)
Do While Not rst.EOF

Y = ReminderLog(rst!UserID, rst!CourseName, rst!EventID, rst!CourseID, ReminderType)

rst.MoveNext
Loop

End Function

Function ReminderLog(UserID, CourseName, EventID, CourseID, ReminderType)

Dim writelog As String

writelog = "INSERT INTO tblReminderLog ( UserID, CourseName, EventID, CourseID, ReminderType ) IN 'J:\0._Administration_of_Training\TASLog.mdb' "

DoCmd.RunSQL writelog

End Function

Obviously, since I am writing here, I need help. The function OneDayReminder actually has more to it, but I took out the parts not necessary for this piece. Basically, it generates an email.

 

[platypus71]

Ok, using this piece of code for the add-to-table, everything works except for some reason, it isn't picking up the rst!UserID piece

writelog = "INSERT INTO tblReminderLog( UserID, CourseName, EventID, CourseID, ReminderType ) IN 'J:\0._Administration_of_Training\TASLog.mdb' VALUES(" & rst!UserID & ",'" & rst!CourseName & "'," & rst!EventID & "," & rst!CourseID & ",'" & ReminderType & "')"

DoCmd.RunSQL writelog

Any ideas?

 

[platypus71]

Nevermind, I figured it out.

I added single quotes around

" & rst!UserID & "

and the problem is solved.

Can someone explain to me why the ' fixed this problem?

Thanks

 

[PHV]

Sems that UserID isn't defined as numeric so you have to use the literal delimiter (single quote)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886