7,732
社区成员
发帖
与我相关
我的任务
分享请发表友善的回复…
发表回复
hycapril 2002-11-18
- 打赏
- 举报
斑竹怎么总写英文？哎！！1
陈年椰子 2002-11-18
- 打赏
- 举报
感谢 inalover(奇遇) ， COPY 下来仔细看
奇遇 2002-11-14
- 打赏
- 举报
Text Import Wizard Does Not Import Data Correctly
This article applies only to a Microsoft Access database (.mdb).
Symptoms
When you use the Import Text Wizard to import certain text files, you may see one of the following symptoms:
The records in the imported file are combined into one continuous record.
The Import Text Wizard stops responding (hangs), or your computer stops processing.
You receive one of the following error messages:
External File isn't in the expected format.
-or-
Out of stack space.
Cause
Certain text files are created with only a Chr(10) (linefeed character) at the end of each line to indicate a new record. To recognize the end of a record, the Import Wizard expects both a Chr(13) and a Chr(10). Furthermore, the character codes must be in this order: Chr(13) + Chr(10). Because Microsoft Access 2000 does not "see" this combination, it interprets the imported data as one record.
Resolution
Use one of the following three methods to work around this behavior.
Method 1
Use a text editor (such as Notepad) to insert a Chr(13) after each record.
Method 2
Use Microsoft Word to search for paragraph marks and replace them with carriage returns/line feeds.
For more information about finding and replacing paragraph marks in Microsoft Word, click Microsoft Word Help on the Help menu, type find and replace text or formatting in the Office Assistant or the Answer Wizard, and then click Search to view the topic.
Method 3
Use the following steps to create a Visual Basic for Applications function that checks to see if a file contains only a CHR(10) at the end of each record and, if so, replaces it with a CHR(13) + CHR(10) so that Microsoft Access is able to successfully import the records.
Start Microsoft Access and open any database.
Create a new module, and then type the following line in the Declarations section if it is not already there:
Option Explicit
Type the following procedures:
Function TestImportText(ImportTextFile As String)
Dim x As String, y As String
Dim NumberOfCarriageReturns As Long
Dim NumberOfLineFeeds As Long
NumberOfCarriageReturns = 0
NumberOfLineFeeds = 0
Open ImportTextFile For Input As #1
Do Until EOF(1)
Line Input #1, x
If InStr(1, x, Chr(13)) > 0 Then
NumberOfCarriageReturns = NumberOfCarriageReturns + 1
End If
If InStr(1, x, Chr(10)) > 0 Then
NumberOfLineFeeds = NumberOfLineFeeds + 1
End If
Loop
Close #1
'If no Carriage returns found, run the next function to modify
'the text file.
If NumberOfCarriageReturns < NumberOfLineFeeds And _
NumberOfLineFeeds > 0 Then
Dim NameOfNewText As String
NameOfNewText = InputBox( _
"Enter The Name Of The New TextFile" _
& " In Which To Save The Changes.")
y = ImportText(ImportTextFile, NameOfNewText)
End If
End Function
Function ImportText(OldText As String, NewText As String)
Dim x As String, Endvalue As Integer
Dim StartValue As Integer, OutputTxt As String
Open OldText For Input As #1
Open NewText For Output As #2
Do Until EOF(1)
Line Input #1, x
Endvalue = InStr(1, x, Chr(10))
StartValue = 1
Do Until Endvalue = 0
If Endvalue > 0 Then
OutputTxt = Mid(x, 1, (Endvalue - 1))
Print #2, OutputTxt
StartValue = Endvalue + 1
x = Mid(x, StartValue)
Endvalue = InStr(1, x, Chr(10))
End If
Loop
Loop
Close #1
Close #2
MsgBox NewText & " Successfully created."
End Function
To run this function, type the following line in the Immediate window, and then press ENTER
? TestImportText(" pathname\filename")
where pathname\filename is the location of your file; for example:
? TestImportText("C:\My Documents\testfile.txt")
If the text file does not need to show carriage returns, an input box appears, asking for the name of the new text file that is going to be created. This prevents the original text file from being overwritten. Type the name of the new text file and click OK. This creates a new text file that is in the proper format for Microsoft Access to import.
This article applies only to a Microsoft Access database (.mdb).
Symptoms
When you use the Import Text Wizard to import certain text files, you may see one of the following symptoms:
The records in the imported file are combined into one continuous record.
The Import Text Wizard stops responding (hangs), or your computer stops processing.
You receive one of the following error messages:
External File isn't in the expected format.
-or-
Out of stack space.
Cause
Certain text files are created with only a Chr(10) (linefeed character) at the end of each line to indicate a new record. To recognize the end of a record, the Import Wizard expects both a Chr(13) and a Chr(10). Furthermore, the character codes must be in this order: Chr(13) + Chr(10). Because Microsoft Access 2000 does not "see" this combination, it interprets the imported data as one record.
Resolution
Use one of the following three methods to work around this behavior.
Method 1
Use a text editor (such as Notepad) to insert a Chr(13) after each record.
Method 2
Use Microsoft Word to search for paragraph marks and replace them with carriage returns/line feeds.
For more information about finding and replacing paragraph marks in Microsoft Word, click Microsoft Word Help on the Help menu, type find and replace text or formatting in the Office Assistant or the Answer Wizard, and then click Search to view the topic.
Method 3
Use the following steps to create a Visual Basic for Applications function that checks to see if a file contains only a CHR(10) at the end of each record and, if so, replaces it with a CHR(13) + CHR(10) so that Microsoft Access is able to successfully import the records.
Start Microsoft Access and open any database.
Create a new module, and then type the following line in the Declarations section if it is not already there:
Option Explicit
Type the following procedures:
Function TestImportText(ImportTextFile As String)
Dim x As String, y As String
Dim NumberOfCarriageReturns As Long
Dim NumberOfLineFeeds As Long
NumberOfCarriageReturns = 0
NumberOfLineFeeds = 0
Open ImportTextFile For Input As #1
Do Until EOF(1)
Line Input #1, x
If InStr(1, x, Chr(13)) > 0 Then
NumberOfCarriageReturns = NumberOfCarriageReturns + 1
End If
If InStr(1, x, Chr(10)) > 0 Then
NumberOfLineFeeds = NumberOfLineFeeds + 1
End If
Loop
Close #1
'If no Carriage returns found, run the next function to modify
'the text file.
If NumberOfCarriageReturns < NumberOfLineFeeds And _
NumberOfLineFeeds > 0 Then
Dim NameOfNewText As String
NameOfNewText = InputBox( _
"Enter The Name Of The New TextFile" _
& " In Which To Save The Changes.")
y = ImportText(ImportTextFile, NameOfNewText)
End If
End Function
Function ImportText(OldText As String, NewText As String)
Dim x As String, Endvalue As Integer
Dim StartValue As Integer, OutputTxt As String
Open OldText For Input As #1
Open NewText For Output As #2
Do Until EOF(1)
Line Input #1, x
Endvalue = InStr(1, x, Chr(10))
StartValue = 1
Do Until Endvalue = 0
If Endvalue > 0 Then
OutputTxt = Mid(x, 1, (Endvalue - 1))
Print #2, OutputTxt
StartValue = Endvalue + 1
x = Mid(x, StartValue)
Endvalue = InStr(1, x, Chr(10))
End If
Loop
Loop
Close #1
Close #2
MsgBox NewText & " Successfully created."
End Function
To run this function, type the following line in the Immediate window, and then press ENTER
? TestImportText(" pathname\filename")
where pathname\filename is the location of your file; for example:
? TestImportText("C:\My Documents\testfile.txt")
If the text file does not need to show carriage returns, an input box appears, asking for the name of the new text file that is going to be created. This prevents the original text file from being overwritten. Type the name of the new text file and click OK. This creates a new text file that is in the proper format for Microsoft Access to import.
奇遇 2002-11-14
- 打赏
- 举报
Sample Code to Import All Database Objects
Summary
This article describes how you to use Data Access Objects (DAO) to import all objects from one Microsoft Access database into the current Access database. In some situations, this code can be used to recover database objects from a corrupted or damaged database that can be opened but cannot be compacted successfully. This code does not import the following elements:
References
Import/Export specifications
Security information (user and group permissions)
The Current User (usually the Administrator) becomes the owner of all imported objects.
More Information
Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact a Microsoft Certified Partner or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Partners, please visit the following Microsoft Web site:
http://www.microsoft.com/partner/referral/
For more information about the support options that are available and about how to contact Microsoft, visit the following Microsoft Web site:
http://support.microsoft.com/default.aspx?scid=fh;EN-US;CNTACTMS
To import all the objects from another database into the current database, follow these steps:
Start Access, and then open the database into which you want to import objects.
This may be a new blank database.
In the Database window, click Modules, and then click New.
On the Tools menu, click References. Make sure that Microsoft DAO 3.0 Object Library or later is selected in the list of references, and then click OK.
Type or paste the following code in the module window:
Option Compare Database
Option Explicit
Public Function ImportDb(strPath As String) As Boolean
On Error Resume Next
Dim db As Database 'Database to import
Dim td As TableDef 'Tabledefs in db
Dim strTDef As String 'Name of table or query to import
Dim qd As QueryDef 'Querydefs in db
Dim doc As Document 'Documents in db
Dim strCntName As String 'Document container name
Dim x As Integer 'For looping
Dim cntContainer As Container 'Containers in db
Dim strDocName As String 'Name of document
Dim intConst As Integer
Dim cdb As Database 'Current Database
Dim rel As Relation 'Relation to copy
Dim nrel As Relation 'Relation to create
Dim strRName As String 'Copied relation's name
Dim strTName As String 'Relation Table name
Dim strFTName As String 'Relation Foreign Table name
Dim varAtt As Variant 'Attributes of relation
Dim fld As Field 'Field(s) in relation to copy
Dim strFName As String 'Name of field to append
Dim strFFName As String 'Foreign name of field to append
'Open database which contains objects to import
Set db = DBEngine.Workspaces(0).OpenDatabase(strPath, True)
'Import tables from specified Access database
For Each td In db.TableDefs
strTDef = td.Name
If Left(strTDef, 4) <> "MSys" Then
DoCmd.TransferDatabase acImport, "Microsoft Access", strPath, acTable, _
strTDef, strTDef, False
End If
Next
'Import queries
For Each qd In db.QueryDefs
strTDef = qd.Name
DoCmd.TransferDatabase acImport, "Microsoft Access", strPath, acQuery, _
strTDef, strTDef, False
Next
'Copy relationships to current database
Set cdb = CurrentDb
For Each rel In db.Relations
With rel
'Get properties of relation to copy
strRName = .Name
strTName = .Table
strFTName = .ForeignTable
varAtt = .Attributes
'Create relation in current db with same properties
Set nrel = cdb.CreateRelation(strRName, strTName, strFTName, varAtt)
For Each fld In .Fields
strFName = fld.Name
strFFName = fld.ForeignName
nrel.Fields.Append nrel.CreateField(strFName)
nrel.Fields(strFName).ForeignName = strFFName
Next
cdb.Relations.Append nrel
End With
Next
'Loop through containers and import all documents
For x = 1 To 4
Select Case x
Case 1
strCntName = "Forms"
intConst = acForm
Case 2
strCntName = "Reports"
intConst = acReport
Case 3
strCntName = "Scripts"
intConst = acMacro
Case 4
strCntName = "Modules"
intConst = acModule
End Select
Set cntContainer = db.Containers(strCntName)
For Each doc In cntContainer.Documents
strDocName = doc.Name
DoCmd.TransferDatabase acImport, "Microsoft Access", strPath, intConst, _
strDocName, strDocName
'Debug.Print strDocName
'for debugging, will list document names in debug window
Next doc
Next x
'Clean up variables to recover memory
Set fld = Nothing
Set nrel = Nothing
Set rel = Nothing
Set cdb = Nothing
Set td = Nothing
Set qd = Nothing
Set cntContainer = Nothing
db.Close
Set db = Nothing
ImportDb = True
End Function
On the View menu, click Debug Window.
In the Debug window, type the following command line, and then press ENTER:
?ImportDb("C:\ pathname\ MySourceDatabase.mdb")
NOTE: Substitute the correct path and file name for the source database. This code returns "True" (or -1) if it runs successfully.
Additional query words: inf
Summary
This article describes how you to use Data Access Objects (DAO) to import all objects from one Microsoft Access database into the current Access database. In some situations, this code can be used to recover database objects from a corrupted or damaged database that can be opened but cannot be compacted successfully. This code does not import the following elements:
References
Import/Export specifications
Security information (user and group permissions)
The Current User (usually the Administrator) becomes the owner of all imported objects.
More Information
Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact a Microsoft Certified Partner or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Partners, please visit the following Microsoft Web site:
http://www.microsoft.com/partner/referral/
For more information about the support options that are available and about how to contact Microsoft, visit the following Microsoft Web site:
http://support.microsoft.com/default.aspx?scid=fh;EN-US;CNTACTMS
To import all the objects from another database into the current database, follow these steps:
Start Access, and then open the database into which you want to import objects.
This may be a new blank database.
In the Database window, click Modules, and then click New.
On the Tools menu, click References. Make sure that Microsoft DAO 3.0 Object Library or later is selected in the list of references, and then click OK.
Type or paste the following code in the module window:
Option Compare Database
Option Explicit
Public Function ImportDb(strPath As String) As Boolean
On Error Resume Next
Dim db As Database 'Database to import
Dim td As TableDef 'Tabledefs in db
Dim strTDef As String 'Name of table or query to import
Dim qd As QueryDef 'Querydefs in db
Dim doc As Document 'Documents in db
Dim strCntName As String 'Document container name
Dim x As Integer 'For looping
Dim cntContainer As Container 'Containers in db
Dim strDocName As String 'Name of document
Dim intConst As Integer
Dim cdb As Database 'Current Database
Dim rel As Relation 'Relation to copy
Dim nrel As Relation 'Relation to create
Dim strRName As String 'Copied relation's name
Dim strTName As String 'Relation Table name
Dim strFTName As String 'Relation Foreign Table name
Dim varAtt As Variant 'Attributes of relation
Dim fld As Field 'Field(s) in relation to copy
Dim strFName As String 'Name of field to append
Dim strFFName As String 'Foreign name of field to append
'Open database which contains objects to import
Set db = DBEngine.Workspaces(0).OpenDatabase(strPath, True)
'Import tables from specified Access database
For Each td In db.TableDefs
strTDef = td.Name
If Left(strTDef, 4) <> "MSys" Then
DoCmd.TransferDatabase acImport, "Microsoft Access", strPath, acTable, _
strTDef, strTDef, False
End If
Next
'Import queries
For Each qd In db.QueryDefs
strTDef = qd.Name
DoCmd.TransferDatabase acImport, "Microsoft Access", strPath, acQuery, _
strTDef, strTDef, False
Next
'Copy relationships to current database
Set cdb = CurrentDb
For Each rel In db.Relations
With rel
'Get properties of relation to copy
strRName = .Name
strTName = .Table
strFTName = .ForeignTable
varAtt = .Attributes
'Create relation in current db with same properties
Set nrel = cdb.CreateRelation(strRName, strTName, strFTName, varAtt)
For Each fld In .Fields
strFName = fld.Name
strFFName = fld.ForeignName
nrel.Fields.Append nrel.CreateField(strFName)
nrel.Fields(strFName).ForeignName = strFFName
Next
cdb.Relations.Append nrel
End With
Next
'Loop through containers and import all documents
For x = 1 To 4
Select Case x
Case 1
strCntName = "Forms"
intConst = acForm
Case 2
strCntName = "Reports"
intConst = acReport
Case 3
strCntName = "Scripts"
intConst = acMacro
Case 4
strCntName = "Modules"
intConst = acModule
End Select
Set cntContainer = db.Containers(strCntName)
For Each doc In cntContainer.Documents
strDocName = doc.Name
DoCmd.TransferDatabase acImport, "Microsoft Access", strPath, intConst, _
strDocName, strDocName
'Debug.Print strDocName
'for debugging, will list document names in debug window
Next doc
Next x
'Clean up variables to recover memory
Set fld = Nothing
Set nrel = Nothing
Set rel = Nothing
Set cdb = Nothing
Set td = Nothing
Set qd = Nothing
Set cntContainer = Nothing
db.Close
Set db = Nothing
ImportDb = True
End Function
On the View menu, click Debug Window.
In the Debug window, type the following command line, and then press ENTER:
?ImportDb("C:\ pathname\ MySourceDatabase.mdb")
NOTE: Substitute the correct path and file name for the source database. This code returns "True" (or -1) if it runs successfully.
Additional query words: inf
hycapril 2002-11-13
- 打赏
- 举报
你想从文本文件倒入啊？
这个问题我没有做过， 不会啊。
关注！！！！！！！！！！！！！
这个问题我没有做过， 不会啊。
关注！！！！！！！！！！！！！
陈年椰子 2002-11-03
- 打赏
- 举报
真的无人能解答？
陈年椰子 2002-11-01
- 打赏
- 举报
hycapril(四月_健康最重要~~~)
能对导入说的具体些吗？
比如要导入temp.txt文件， 如何写
temp表： name char(20) , age int
temp.txt :
name1 ; 20
name2 ; 22
name3 ; 21
能对导入说的具体些吗？
比如要导入temp.txt文件， 如何写
temp表： name char(20) , age int
temp.txt :
name1 ; 20
name2 ; 22
name3 ; 21
hycapril 2002-10-31
- 打赏
- 举报
可以用sql语句实现啊
清空表 docmd.runsql "delete * from 表名"
倒入数据 docmd.runsql "insert * ........"
清空表 docmd.runsql "delete * from 表名"
倒入数据 docmd.runsql "insert * ........"
