Import ASP Excel Data to access, sample code in SQL Server

Import ASP Excel Data to access, sample code in SQL Server
SQL serverexcelsqlaccessasp

Import the data in Excel to access, provided that the table in access has been created.

Dim Conn
Dim conn2
Set conn = Createobject ("ADODB. Connection ")
Conn. Open "provider = Microsoft. Jet. oledb.4.0; Jet oledb: Database Password =; Data Source = c: \ book1.mdb"

Set conn2 = Createobject ("ADODB. Connection ")
Conn2.open "provider = Microsoft. Jet. oledb.4.0; Jet oledb: Database Password =; extended properties = Excel 5.0; Data Source = c: \ book1.xls"

SQL = "select * from [sheet1 $]"
Set rs = conn2.execute (SQL)
While not Rs. EOF
SQL = "insert into XXX ([a], [B], [c], [d]) values ('" & fixsql (RS (0 ))&"', '"& fixsql (RS (1) &"', '"& fixsql (RS (2) &"', '"& fixsql (RS (3 )) &"')"
Conn.exe cute (SQL)
Rs. movenext
Wend

Conn. Close
Set conn = nothing
Conn2.close
Set conn2 = nothing

Function fixsql (STR)
Dim newstr
Newstr = Str
If isnull (newstr) then
Newstr = ""
Else
Newstr = Replace (newstr ,"'","''")
End if
Fixsql = newstr
End Function

When importing data to an SQL Server database, if the Excel file is not on the same server as the database, refer to the above Code. On the same machine, you can refer to the following code (without having to create a table first, the program will create the table by itself, and use the first row of data in Excel as the table field name ):

Dim Conn
Set conn = Createobject ("ADODB. Connection ")
Conn. Open ("driver = {SQL Server}; server = localhost; uid = sa; Pwd = sa; database = hwtemp ;")
SQL = "select * into newtable from OpenDataSource ('Microsoft. jet. oledb.4.0 ', 'Data source = "C: \ book1.xls" "; User Properties = Excel 5.0 ')... [sheet1 $]"

Conn.exe cute (SQL)

Conn. Close
Set conn = nothing