I've uploaded a pretty large database with the following code and it
didn't take a long time. See macro below
You could export your spreadshet as CSV and then import the data into
the SQL server in CSV format.
Sub Submit()
'filename of database is with MakeDatabase macro
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Set sht = ThisWorkbook.Sheets("USA")
strdb = Folder & FName
If Dir(strdb) = "" Then
MsgBox ("Database Doesn't Exists, Create Database" & strdb)
MsgBox ("Exiting Macro")
Exit Sub
End If
ConnectStr = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Folder & FName & ";" & _
"Mode=Share Deny None;"
cn.Open (ConnectStr)
With rs
.Open Source:="USA", _
ActiveConnection:=cn, _
CursorType:=adOpenDynamic, _
LockType:=adLockOptimistic, _
Options:=adCmdTable
If .EOF < True Then
.MoveLast
End If
End With
LastCol = sht.Cells(1, Columns.Count).End(xlToLeft).Column
LastRow = sht.Range("A" & Rows.Count).End(xlUp).Row
For RowCount = 2 To LastRow
With rs
.AddNew
!ID = sht.Cells(RowCount, "A")
For ColCount = 2 To LastCol
If Data < "" Then
ColName = sht.Cells(1, ColCount)
rs(ColName) = sht.Cells(RowCount, ColCount)
End If
Next ColCount
.Update
End With
Next RowCount
Set appAccess = Nothing
End Sub
--
joel
------------------------------------------------------------------------
joel's Profile:
http://www.thecodecage.com/forumz/member.php?u=229
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=198561
http://www.thecodecage.com/forumz