There may be a similar function, but I'll leave that to others to comment on. I have always set up a connection, and then build and run SQL strings to accomplish this. I used the following code to create a table called RBRVS and then insert data into it from another table. There is some additional logic included, but this should be able to give you a basic idea. Yell if it needs more clarification.
Mike
dim cn as ADODB.Connection
dim rs as ADODB.Recordset
Set cn = Application.CurrentProject.Connection
Set rs = New ADODB.Recordset
'This checks to see if the table exists
strSQL = "SELECT Count(*) AS Present FROM MSysObjects WHERE Name = 'RBRVS' AND Type = 1"
rs.Open strSQL, cn
'If the table exists, we need to drop it
If rs.Fields("Present") = 1 Then
strSQL = "DROP TABLE RBRVS"
cn.Execute strSQL
End If
strSQL = "CREATE TABLE RBRVS "
strSQL = strSQL + "(Code TEXT(5) NOT NULL, Description TEXT(100) NOT NULL, "
strSQL = strSQL + "WorkRVU DOUBLE, PracticeRVU DOUBLE, MalpracticeRVU DOUBLE, "
strSQL = strSQL + "TotalRVU TEXT(15), Amount CURRENCY)"
cn.Execute strSQL
Set rs = New ADODB.Recordset
strSQL = "SELECT * FROM " + OtherTable
rs.Open strSQL, cn
rs.MoveFirst
While rs.EOF = False
strSQL = "INSERT INTO RBRVS VALUES ( '"
strSQL = strSQL & rs.Fields("Anesthesia_Code") & "', '" & rs.Fields("Description") & "', "
strSQL = strSQL & "NULL, NULL, NULL, '" & rs.Fields("Anesthesia_Base_Unit") & "', "
If IsNumeric(rs.Fields("Anesthesia_Base_Unit")) Then
strSQL = strSQL & (rs.Fields("Anesthesia_Base_Unit") * ANESCF) & ")"
Else
strSQL = strSQL & "0.00)"
End If
cn.Execute strSQL
rs.MoveNext
Wend
set rs = Nothing
set cn = Nothing
Mike
EchoVue.com
|