Excel data as a sequence number date supplier other customer number 1 access other fields as follows: document number (primary key) date supplier customers

Realize functions: 1, the excel data 2 in addition to the serial number into the access database, document number according to the date of the excel in access to the largest number of add + 1, if there is no begin from 1 row
3, document numbering rules for the cw + date + 4 serial number (180828) (0001)

How to operate, so please give me some guidance,

CodePudding user response:

Basically two types of methods:

1 will Excel as an engine of ADO database to Access external database, direct import Access database by SQL statements to the table,

2 open the worksheet with Excel object, detailed records inserted into the open Access database with ADO objects recordset object,

CodePudding user response:

Method 1 use the example of the DAO (similar to use ADO) :

Need to refer to DAO and ADO object,

 
'* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * 
'how to Excel file import Access? 
'* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * 
'call the process a total of four parameters: 
'1, sSheetName: want to export the data file name (Sheet name), for example Sheet1 
'2, sExcelPath: to export to Excel data file path name (Workbook path), such as C: \ book1 XLS 
'3, sAccessTable: you want to import the Access Table name, such as TestTable 
'4, sAccessDBPath: you want to import the Access file path name, such as C: \ Test. The MDB 

'the process statement: 

Private Sub ExportExcelSheetToAccess (sSheetName As String, sExcelPath As String, sAccessTable As String, sAccessDBPath As String) 
Dim the db As the Database 
Dim the rs As you 
Set the db=OpenDatabase (sExcelPath, True, False, "5.0" Excel) 
Call the Execute (" Select * into [the database="& amp; sAccessDBPath & amp;"]. "& amp; SAccessTable & amp; "FROM [" & amp; sSheetName & amp;" $] ") 
MsgBox "Table exported successfully." vbInformation, "Yams 
"End Sub 

 
'using examples are as follows: C: \ book1. XLS Sheet1 import in the C: \ Test. The MDB become TestTable 

'ExportExcelSheetToAccess "Sheet1", "C: \ book1 XLS", "TestTable", "C: \ Test. MDB" 

CodePudding user response:

ADO connection string to Excel:

 "Provider=Microsoft. Jet. The OLEDB. 4.0; The Data Source=C: \ MyExcel. XLS; Extended Properties=8.0 "" Excel; HDR=Yes; IMEX=1 "" "

HDR said column header lines
IMEX digital mixing column as text column processing text

CodePudding user response:

I can now be imported into a temporary table, but how to restart again into a formal table?

CodePudding user response:

You put the temporary table name into a formal table name is ok, if you have a formal table automatic number field, a new record for each automatically assigned a number,

CodePudding user response:

refer to the second floor of123 response:

method 1 example of using the DAO (similar to use ADO) :

Need to refer to DAO and ADO object,

 
'* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * 
'how to Excel file import Access? 
'* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * 
'call the process a total of four parameters: 
'1, sSheetName: want to export the data file name (Sheet name), for example Sheet1 
'2, sExcelPath: to export to Excel data file path name (Workbook path), such as C: \ book1 XLS 
'3, sAccessTable: you want to import the Access Table name, such as TestTable 
'4, sAccessDBPath: you want to import the Access file path name, such as C: \ Test. The MDB 

'the process statement: 

Private Sub ExportExcelSheetToAccess (sSheetName As String, sExcelPath As String, sAccessTable As String, sAccessDBPath As String) 
Dim the db As the Database 
Dim the rs As you 
Set the db=OpenDatabase (sExcelPath, True, False, "5.0" Excel) 
Call the Execute (" Select * into [the database="& amp; sAccessDBPath & amp;"]. "& amp; SAccessTable & amp; "FROM [" & amp; sSheetName & amp;" $] ") 
MsgBox "Table exported successfully." vbInformation, "Yams 
"End Sub 

 
'using examples are as follows: C: \ book1. XLS Sheet1 import in the C: \ Test. The MDB become TestTable 

'ExportExcelSheetToAccess "Sheet1", "C: \ book1 XLS", "TestTable", "C: \ Test. MDB" 

Thank you very much!

CodePudding user response:

Thank you expert, the collection, study recently prepared with EXCEL import ACCESS, very useful

CodePudding user response:

If renumbered after import access?

CodePudding user response:

Excel guide access
The best thing to do is call the access application object. The equivalent of you choose to import excel table in access, both speed and accuracy are far more than any other means.
Is any other means ", no one (compared to open the excel object to read data with a database link write access) an order of magnitude faster.
You may be a reference of the most commonly used excel object, but also can be referenced in vb access application.
Specific approach to the upper right corner.

CodePudding user response:

Some time ago to write a small program, provide ideas for your reference: when importing data, only take the needed data table, and as for the arrangement to cope with different fields of form can be adjusted at any time corresponding relationship can,

Wanted to upload software screenshots, but I don't know today I return a responsibility, always can't upload pictures, only a brief description solution,

CodePudding user response:

Just said 1, didn't say 2, 3,
According to the description, you need to number is even number? If it is even number, right ah, directly on the large size as + 1; No data that day, and are numbered from 1 to start, just done,
, don't even go directly for signature + millisecond time number, line,