how to append a table from ms access front end to back end.

[icemonster]

so i have this string:

                          strSQL = "INSERT INTO tbl_school " & _
                                   "(school_name, school_degree, school_major, school_startdate, school_enddate) SELECT (school_name, school_degree, " _
                                 & "school_major, school_startdate, school_enddate) FROM tmptbl_school;"
                          .Execute strSQL, , adCmdText + adExecuteNoRecords
                          strSQL = "SELECT Last_Insert_ID();"
                            With .Execute(strSQL, , adCmdText)
                                If Not (.BOF And .EOF) Then
                                    ' Found the new ID - build the second Insert SQL string
                                    lngLastSchoolID = .Fields(0)
                                Else
                                    ' Abort
                                End If
                            .Close
                            End With

the thing is tmptbl_school is wihtin the ms access front end. so it keeps telling me that the table does not exist which makes a lot of sense, does anyone know how to correct my syntax?

 

[SQL_Hell]

Hi there

Did you fix this? surely you just need to change the connection string to point at the access database rather than sql server

 

[icemonster]

not really no, i have put this problem on the back burner for the mean time. do you know how to convert it with the access connection?

 

[SQL_Hell]

Whats the value of adCmdText?