insert into ...
or
select ..into Dim backm As Database
SQL = ""
On Error Resume Next
jdt.Max = 180
jdt = 1
Kill App.Path + "\backup\" + Trim(Text2)
FileCopy App.Path + "\backup\backup.mdb", App.Path + "\backup\" + Trim(Text2)
jdt = 10
Set backm = Workspaces(0).OpenDatabase("ci", dbDriverNoPrompt, False, "DSN=czic;UID=sa;PWD=;DATABASE=ci;")
''dbsql.Execute "select * into [;database=C:\quenry\shzc\temp.mdb].sghtmx from buycondetail where 1=2 "
jdt = 20
SQL = "select * into [;database=" + App.Path + "\backup\" + Trim(Text2) + "].sys_log from sys_log where 1=1"
backm.Execute SQL
or
select ..into Dim backm As Database
SQL = ""
On Error Resume Next
jdt.Max = 180
jdt = 1
Kill App.Path + "\backup\" + Trim(Text2)
FileCopy App.Path + "\backup\backup.mdb", App.Path + "\backup\" + Trim(Text2)
jdt = 10
Set backm = Workspaces(0).OpenDatabase("ci", dbDriverNoPrompt, False, "DSN=czic;UID=sa;PWD=;DATABASE=ci;")
''dbsql.Execute "select * into [;database=C:\quenry\shzc\temp.mdb].sghtmx from buycondetail where 1=2 "
jdt = 20
SQL = "select * into [;database=" + App.Path + "\backup\" + Trim(Text2) + "].sys_log from sys_log where 1=1"
backm.Execute SQL
我想在ADO中 用 "insert into a_mdb select * from b_sql" ,其中A_MDB 是ACCESS,b_SQL 是SQL 6.5,来实现不同数据源的记录追加,如何实现?
我已通过 B_SQL.GETROWS(N) 和 A_MDB.ADDNEW FIELDSNAME,FIELDSVALUE 分步实现.代码如下:'取得SQL SERVER 6.5 中B_SQL
Conn_sql.ConnectionString = "FILE NAME=C:\Program Files\Common Files\ODBC\Data Sources\sql_subserver.dsn"
Conn_sql.Open ,uid, pwd
Set Rs_sql = Conn_sql.Execute("select * from b_sql")'取得MDB 中的 A_MDB
Conn_mdb.ConnectionString = "Provider=Microsoft.Jet.OLEDB.3.51;Persist Security Info=False;Data Source=ribao.mdb"
Conn_mdb.Open
Rs_mdb.CursorLocation = adUseClient
Rs_mdb.Open "a_mdb", Conn_mdb, adOpenKeyset, adLockOptimistic'追加数据:B_SQL--->A_MDB
Dim Rsdata(), Rowcount, i As Integer
While Not Rs_sql.EOF
Rsdata = Rs_sql.GetRows(100) '每次读取100条记录。偶尔发现如果大于100会造成读取记录总数不可预料的错误!!!
Rowcount = UBound(Rsdata, 2)
For i = 0 To Rowcount
Rs_mdb.AddNew Array("尺寸", "型号", "性质", "数量", "日期"), Array(Trim(Rsdata(0, i)), Trim(Rsdata(1, i)), Trim(Rsdata(2, i)), Trim(Rsdata(3, i)), Trim(Rsdata(4, i)))
Next i
Wend
但我想能用前述SQL实现效率更高.
例一:
Dim conn As New ADODB.Connection
conn.Open "Provider=Microsoft.Jet.OLEDB.3.51;Persist Security Info=False;Data Source=ribao.mdb"
conn.Execute " insert into a_mdb select * from b_sql in ''[odbc;database=newstardbase; uid=doman_chj; pwd=1998;dsn='d:\cw\sql_subserver.dsn']"运行后提示:实时错误:-2147467259 (80004005) odbc--连接到'd:\cw\sql_subserver.dsn' 失败例二:
Dim conn As New ADODB.Connection
conn.ConnectionString = "FILE NAME=d:\cw\sql_subserver.dsn"
conn.Open ,uid,pwd
conn.Execute " insert into a_mdb in 'd:\ribao.mdb' select * from b_sql"运行后提示:实时错误:-2147217900 (80040e14) [microsoft] [odbc sql server driver] [sql server] incorrect
syntax near the keyword 'in' 类似的问题:1、怎样用VB ado 实现同步sql6.5 和 sql7.0的相关数据?(公司有两个服务器,两个软件,一个用SQL6.5,一个用SQL7.0)
2、有没有实现两个不同数据库数据转换的VB控件?(听朋友说好像delphi中有类似的控件).以上问题请高手不吝赐教!!! 不胜感谢。
mymail: [email protected]