用DAO,我给你一个例子,用来创建和修改传递查询的,希望对你有启发:
Public Function ClientServerX(SVC As String, VSL As String, VOY As String, DIR As String, POL As String) As Boolean
Dim dbsCurrent As Database
Dim qdfPassThrough As QueryDef
Dim qdfLocal As QueryDef
Dim rstTopFive As Recordset
Dim strMessage As String
On Error GoTo loop1
Set dbsCurrent = CurrentDb
'CREATE ODSTRUNK TABLE
Set qdfPassThrough = dbsCurrent.CreateQueryDef("ODSTRUNK")
qdfPassThrough.Connect = "ODBC;DSN=ODSPRO;SRVR=ODSPRO;DB=dbfabcp1;UID=FABC_MAINT;PWD=FABC_MAINT"
qdfPassThrough.SQL = "SELECT A.TRCKG_GRP_REF_CDE, A.LDD_SVC_LOOP, A.LDD_VSL_CDE, A.LDD_VOY, A.LDD_DIR, A.POL, A.POD, A.SEQ_NUM, B.CNTR_TYPE, B.COMMIT_STATUS FROM TRUNK_LEG A, CONTAINER_PACKAGE B WHERE LDD_SVC_LOOP='" & SVC & "' AND LDD_VSL_CDE='" & VSL & "' AND LDD_VOY='" & VOY & "' AND LDD_DIR='" & DIR & "' AND POL='" & POL & "' AND A.PACKAGE_UUID=B.PACKAGE_UUID"
qdfPassThrough.ReturnsRecords = True
'CREATE ODSSC TABLE
Set qdfPassThrough = dbsCurrent.CreateQueryDef("ODSSC")
qdfPassThrough.Connect = "ODBC;DSN=ODSPRO;SRVR=ODSPRO;DB=dbfabcp1;UID=FABC_MAINT;PWD=FABC_MAINT"
qdfPassThrough.SQL = "SELECT DISTINCT B.TRCKG_GRP_REF_CDE, B. TRCKG_GRP_EXT_REF_CDE, B.EXT_REF FROM TRUNK_LEG A, TRCKG_GRP_EXT_REF B WHERE LDD_SVC_LOOP='" & SVC & "' AND LDD_VSL_CDE='" & VSL & "' AND LDD_VOY='" & VOY & "' AND LDD_DIR='" & DIR & "' AND POL='" & POL & "' AND A.TRCKG_GRP_UUID=B.TRCKG_GRP_UUID AND (B.EXT_REF='03' OR B.EXT_REF='05')"
qdfPassThrough.ReturnsRecords = True
'CREATE ODSSHPR TABLE
Set qdfPassThrough = dbsCurrent.CreateQueryDef("ODSSHPR")
qdfPassThrough.Connect = "ODBC;DSN=ODSPRO;SRVR=ODSPRO;DB=dbfabcp1;UID=FABC_MAINT;PWD=FABC_MAINT"
qdfPassThrough.SQL = "SELECT B.TRCKG_GRP_REF_CDE,B.SHPR_NME FROM TRUNK_LEG A, CONTAINER_PACKAGE B WHERE LDD_SVC_LOOP='" & SVC & "' AND LDD_VSL_CDE='" & VSL & "' AND LDD_VOY='" & VOY & "' AND LDD_DIR='" & DIR & "' AND POL='" & POL & "' AND A.PACKAGE_UUID=B.PACKAGE_UUID"
qdfPassThrough.ReturnsRecords = True
' 'CREATE ODSBLSC TABLE
' Set qdfPassThrough = dbsCurrent.CreateQueryDef("ODSBLSC")
' qdfPassThrough.Connect = "ODBC;DSN=ODSPRO;SRVR=ODSPRO;DB=dbfabcp1;UID=FABC_MAINT;PWD=FABC_MAINT"
'
' qdfPassThrough.SQL = "SELECT A.TRCKG_GRP_REF_CDE, A.BL_REF_CDE, B.REF_CDE,B.REF_TYPE FROM BL_TRCKG_GRP_ASSN A, BL_EXT_REF B WHERE A.BL_BASIC_UUID=B.BL_BASIC_UUID AND (B.REF_TYPE='03' OR B.REF_TYPE='05') AND A.TRCKG_GRP_UUID IN (SELECT TRCKG_GRP_UUID FROM TRUNK_LEG A WHERE A.LDD_SVC_LOOP='" & SVC & "' AND A.LDD_VSL_CDE='" & VSL & "' AND A.LDD_VOY='" & VOY & "' AND A.LDD_DIR='" & DIR & "' AND A.POL='" & POL & "')"
' qdfPassThrough.ReturnsRecords = True
'
' 'CREATE ODSBLSTATE TABLE
' Set qdfPassThrough = dbsCurrent.CreateQueryDef("ODSBLSTATE")
' qdfPassThrough.Connect = "ODBC;DSN=ODSPRO;SRVR=ODSPRO;DB=dbfabcp1;UID=FABC_MAINT;PWD=FABC_MAINT"
'
' qdfPassThrough.SQL = "SELECT A.TRCKG_GRP_REF_CDE, A.BL_REF_CDE, B.BL_STATE FROM BL_TRCKG_GRP_ASSN A, BL_BASIC B WHERE A.BL_BASIC_UUID=B.BL_BASIC_UUID AND A.TRCKG_GRP_UUID IN (SELECT TRCKG_GRP_UUID FROM TRUNK_LEG A WHERE A.LDD_SVC_LOOP='" & SVC & "' AND A.LDD_VSL_CDE='" & VSL & "' AND A.LDD_VOY='" & VOY & "' AND A.LDD_DIR='" & DIR & "' AND A.POL='" & POL & "')"
' qdfPassThrough.ReturnsRecords = True
DoCmd.RunSQL ("DELETE * FROM TRUNK;")
DoCmd.RunSQL ("INSERT INTO TRUNK SELECT ODSTRUNK.* FROM ODSTRUNK;")
DoCmd.RunSQL ("DELETE * FROM SC;")
DoCmd.RunSQL ("INSERT INTO SC SELECT ODSSC.* FROM ODSSC;")
DoCmd.RunSQL ("DELETE * FROM SHPR;")
DoCmd.RunSQL ("INSERT INTO SHPR SELECT DISTINCT ODSSHPR.* FROM ODSSHPR;")
' DoCmd.RunSQL ("DELETE * FROM BLSC;")
' DoCmd.RunSQL ("INSERT INTO BLSC SELECT ODSBLSC.* FROM ODSBLSC;")
' DoCmd.RunSQL ("DELETE * FROM BLSTATE;")
' DoCmd.RunSQL ("INSERT INTO BLSTATE SELECT ODSBLSTATE.* FROM ODSBLSTATE;")