Hente fra 2 forskellige tabeller i Access og skrive dem til en ny tabel

insert into tabel3
select table1.kolonne1, tabel1.kolonne2, table2.kolonne3, tabel1.kolonne3;

Du behøver ikke et script - du kan køre en query direkte ;o)

øhhh - der er stavefejl - ups ...

jeg ønsker et script, da jeg skal have bygget videre på det samt køre et scheduled job f.eks. hver time.

Så kan jeg hente fra en tabel men hvordan returnerer jeg data med f.eks. tab imellem (nu står de under hinanden):

______________________

On Error Resume Next

Const adOpenStatic = 3
Const adLockOptimistic = 3

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

objConnection.Open _
    "Provider=Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source=d:\metro2\klausen.mdb"

objRecordSet.Open "SELECT * FROM name ORDER BY userID ASC, surname ASC " , _
    objConnection, adOpenStatic, adLockOptimistic



objRecordSet.MoveFirst

Do Until objRecordSet.EOF
Wscript.Echo objRecordSet.Fields.Item("firstname")
Wscript.Echo objRecordSet.Fields.Item("userid")
Wscript.Echo objRecordSet.Fields.Item("surname")
    objRecordSet.MoveNext
Loop

så fik jeg tab i mellem, men hvordan skriver jeg resultatet til en fil?

On Error Resume Next

Const adOpenStatic = 3
Const adLockOptimistic = 3

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

objConnection.Open _
    "Provider=Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source=d:\klausen.mdb"

objRecordSet.Open "SELECT * FROM name ORDER BY emp_no ASC" , _
    objConnection, adOpenStatic, adLockOptimistic



objRecordSet.MoveFirst

Do Until objRecordSet.EOF
    Wscript.Echo objRecordSet.Fields.Item("emp_no") & vbTab & objRecordSet.Fields.Item("first_name")& vbTab & objRecordSet.Fields.Item("surname")
    objRecordSet.MoveNext
Loop

Så virker den!

'setup connection
Dim objConn
Set objConn = CreateObject("ADODB.Connection")

On Error Resume Next
Dim fsoObject, open_File, target_File
Set fsoObject = WScript.CreateObject("Scripting.FileSystemObject")
target_File = "C:\Output.txt"
'open your odbc d:\[dir]\[my database].mdb
objConn.Open "TestDB"




Dim objRS
Set objRS = CreateObject("ADODB.Recordset")
objRS.Open "SELECT * FROM name Order by Emp_no ASC", objConn, 2

If objRS.EOF And objRS.BOF Then
'empty recordset
End If


objRS.MoveFirst
Open_My_File()

Do Until objRS.EOF = True
    strSurname = objRS("surname")
    strEmp_no = objRS("emp_no")
    strFirst_name = objRS("first_name")
    objRS.MoveNext
    'WScript.Echo strEmp_no & vbTab & strFirst_name & vbTab & strSurname
    open_File.WriteLine strEmp_no & ";" & strFirst_name & ";" & strSurname
Loop

Close_My_File()
objRS.Close


' *********** Procedures go here *************
' This function opens a file
Function Open_My_File()
If (fsoObject.FileExists(target_File)) Then
Set open_File = fsoObject.OpenTextFile(target_File, 8)
Else
Set open_File = fsoObject.OpenTextFile(target_File, 2, "True")
End If
End Function



' This function closes a file
Function Close_My_File()
open_File.Close()
End Function

WScript.Echo "records written to c:\Output.txt"