Importing Data into Access

Can anyone tell me if there is a way of importing data into Access using VB, without creating and running a macro?

Plenty. Countless.
But Which one do you want?
It is more difficult to choose than telling you Yes or No.

AnhMy_Tran

Well. I have a script, such as below, that opens my database, performs various queries on it and then closes it but before I perform these queries I need to import new tables from Excel spreadsheets found in set locations.

What is the easiest way of doing this?

strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=c:\test.mdb"
Set Conn = New ADODB.Connection
Conn.Open strConn

' import new data. How?

Set rs = New ADODB.Recordset
strSql = "SELECT * FROM table1"
rs.Open strSql, Conn, adOpenDynamic, adLockOptimistic, adCmdText

'perform various functions on rs recordset

rs.Close
Set rs = Nothing

The following is the actual program I posted on Help.com:
Please, pay attention that I have several different data type.

Option Explicit

Dim wkbObj As Workbook

Private Sub Command1_Click()
' This is MS ADO 2.5 Library
Dim cnn As New ADODB.Connection
cnn.CursorLocation = adUseClient
cnn.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & App.Path & "\Test.mdb;"
cnn.Open

Dim lngRowNum As Long ' it is the row counter
' Define all columns, and the rows can be infinite
Dim strSQL As String, strA As String, strB As String
Dim intC As Integer, lngD As Long, dblE As Double, datF As Date

lngRowNum = 1
cnn.BeginTrans
With wkbObj.Worksheets(1)
While Not .Range("A" & CStr(lngRowNum)).Value = vbNullString
strA = .Range("A" & CStr(lngRowNum)).Value
strB = .Range("B" & CStr(lngRowNum)).Value
intC = .Range("C" & CStr(lngRowNum)).Value
lngD = .Range("D" & CStr(lngRowNum)).Value
dblE = .Range("E" & CStr(lngRowNum)).Value
datF = .Range("F" & CStr(lngRowNum)).Value
strSQL = "INSERT INTO Table1 (StringA, StringB, " & _
"IntegerC, LongD, DoubleE, DateF) " & _
"VALUES ('" & strA & "', '" & strB & "', " & _
intC & ", " & lngD & ", " & dblE & ", #" & datF & "#);"
cnn.Execute strSQL
lngRowNum = lngRowNum + 1
Wend
End With
cnn.CommitTrans
End Sub

Private Sub Form_Load()
Set wkbObj = GetObject(App.Path & "\test.xls")
End Sub


AnhMy_Tran