请教高手一个问题

wushenren7

表一：
张三        100
李四        300
王二        500

表二：
员工        工资

怎么样在ACCESS里将标题加进去，变成表三这样：
员工        工资
张三        100
李四        300
王二        500

因为实际数据很大，只能导入ACCESS中处理，而无法在EXCEL里直接做。请教高手！

Henry D. Sy

1. 请注意使用简单明了的标题提问
2.假设结果表名为a

1. SELECT 表.字段1 AS 员工, 表.字段2 AS 工资 INTO a
   
2. FROM 表

复制代码

wushenren7

Henry D. Sy 发表于 2012-5-7 14:16
1. 请注意使用简单明了的标题提问
2.假设结果表名为a

谢谢，下次注意！
其实我的实际表中有几十个字段，我更想能自动添加标题，而不是一个个手动输进去，有什么办法吗？

Henry D. Sy

能告诉我如何知道字段1......n所对应的关系吗

Henry D. Sy

1. Private Sub Command0_Click()
   
2. 
   
3.     Dim rs As New ADODB.Recordset
   
4.     Dim cnn As New ADODB.Connection
   
5.     Dim strFld() As String
   
6.     Dim strSQL As String
   
7.     Dim i As Integer
   
8.    
   
9.     Set cnn = CurrentProject.Connection
   
10.    
    
11.     rs.Open "标题", cnn, adOpenKeyset, adLockReadOnly
    
12.    
    
13.     ReDim strFld(1 To rs.Fields.Count)
    
14.    
    
15.     For i = 1 To rs.Fields.Count
    
16.         strFld(i) = rs.Fields(i - 1)
    
17.     Next
    
18.    
    
19.     rs.Close
    
20.    
    
21.     rs.Open "表", cnn, adOpenKeyset, adLockReadOnly
    
22.    
    
23.     For i = 1 To rs.Fields.Count
    
24.         strSQL = strSQL & "字段" & i & " as " & strFld(i) & ","
    
25.     Next
    
26.    
    
27.     If Len(strSQL) <> 0 Then
    
28.         strSQL = Left(strSQL, Len(strSQL) - 1)
    
29.     End If
    
30.    
    
31.     strSQL = "select " & strSQL & " into A from 表"
    
32.    
    
33.     CurrentDb.Execute strSQL
    
34.    
    
35.     DoCmd.OpenTable "A"
    
36.    
    
37.     Set rs = Nothing
    
38.     Set cnn = Nothing
    
39.    
    
40. End Sub
    

复制代码

再次运行代码时，还需判断A是否存在，如有则先删除它

Henry D. Sy

1. Public Function tblExist(tblName As String) As Boolean
   
2.     Dim tbl As TableDef
   
3.     tblExist = False
   
4.     For Each tbl In CurrentDb.TableDefs
   
5.         If tbl.Name = tblName Then
   
6.             tblExist = True
   
7.             Exit For
   
8.         End If
   
9.     Next
   
10. End Function
    

复制代码

1. Private Sub Command0_Click()
   
2.     Dim rs As New ADODB.Recordset
   
3.     Dim cnn As New ADODB.Connection
   
4.     Dim strFld() As String
   
5.     Dim strSQL As String
   
6.     Dim i As Integer
   
7.     Set cnn = CurrentProject.Connection
   
8.     If tblExist("A") Then
   
9.         DoCmd.DeleteObject acTable, "A"
   
10.     End If
    
11.     rs.Open "标题", cnn, adOpenKeyset, adLockReadOnly
    
12.     ReDim strFld(1 To rs.Fields.Count)
    
13.     For i = 1 To rs.Fields.Count
    
14.         strFld(i) = rs.Fields(i - 1)
    
15.     Next
    
16.     rs.Close
    
17.     rs.Open "表", cnn, adOpenKeyset, adLockReadOnly
    
18.     For i = 1 To rs.Fields.Count
    
19.         strSQL = strSQL & "字段" & i & " as " & strFld(i) & ","
    
20.     Next
    
21.     If Len(strSQL) <> 0 Then
    
22.         strSQL = Left(strSQL, Len(strSQL) - 1)
    
23.     End If
    
24.     strSQL = "select " & strSQL & " into A from 表"
    
25.     CurrentDb.Execute strSQL
    
26.     DoCmd.OpenTable "A"
    
27.     Set rs = Nothing
    
28.     Set cnn = Nothing
    
29. End Sub
    

复制代码