Move data from excel to Access

[Newby]

I am treying to move data from an Excel worksheet to Access. My code is below. My program does not like my "Set rs = OpenTable("GL")" line


Option Explicit

Public Const gconConnection = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source='C:\users\c156281\my documents\PLAN\Plan New.accdb'"
Public rsData As DAO.Recordset

Sub PostData()
'TryPostAgain:
'On Error GoTo BadPost
'On Error GoTo 0
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim Stringsql As String
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
cn.Open gconConnection
Dim Sht As Worksheet
Set Sht = Worksheets("ImportSht")

Dim lngFieldNameRow As Long
Dim lngLastPostRow As Long
Dim lngFlagColumn As Long
Dim lngLastColumn As Long


initGlobals

lngFieldNameRow = Sht.Range("ImportHeaderRow").Row

lngLastColumn = 35

Dim strCurrentField As String

'Dim rs As Recordset
Set rs = OpenTable("GL")

Dim i As Long
Dim j As Long

'i = lngLastPostRow
i = lngFieldNameRow + 1
j = Sht.Range("ImportHeaderRow").Column

'Start at the beginning and go until you see a blank first cell in a row
While Sht.Cells(i, j).Value <> ""
'Check to see if the column should be posted

rs.AddNew
'Add the record entries
While j <= lngLastColumn
strCurrentField = Sht.Cells(lngFieldNameRow, j)
If strCurrentField <> "" Then
rsFinancialData(strCurrentField) = Sht.Cells(i, j).Value

End If
j = j + 1
Wend


rs("Computer") = getMachineName
rs("User") = getUserName


rs("DatePost") = Now
rs("Prgm") = Vbl.Range("FileName")

rs.Update



j = Sht.Range("ImportHeaderRow").Column ' Restate column position for next record
i = i + 1
Wend
rs.Close
Exit Sub
BadPost:
Msg = "Sorry you are not able to post your records at this time."
Msg = Msg & vbNewLine
Msg = Msg & "Would you like to email a copy of your parking lot record to yourself and finance? "
Msg = Msg & vbNewLine
Msg = Msg & vbNewLine
Msg = Msg & "Entering NO will erase all unposted entry. "
Ans2 = MsgBox(Msg, vbYesNoCancel)
'parklot.Unprotect ("john")
'If Ans2 = vbYes Then
' Vbl.Range("PostErrorFlag") = "1"
' UF_Email.Show
' parklot.Range("Parking_Lot").Clear
' ElseIf Ans2 = vbCancel Then
'' Cancel = True
' Exit Sub
' ElseIf Ans2 = vbNo Then
' parklot.Range("Parking_Lot").Clear
' ' Unload UF_Initial_Login
' UF_Intial_Login.Show
' End If

End Sub

[rpeare]

You can't open a recordset without telling your code which database you're using. If you're using your current database you'd have to declare it like:

dim db as adodb.database

set db = currentdb

[Newby]

You can't open a recordset without telling your code which database you're using. If you're using your current database you'd have to declare it like:

dim db as adodb.database

set db = currentdb

I am currently calculating a rolling 12 month average and I am set up a loop and I want to reference fields by position. e.g. last 4 of 2011 and first 8 months of 2012. i used MoveNext to go to the next record. Is there a similar reference to next or previoius fields?

[rpeare]

you can reference columns in a dataset by either the name of the field or a field position

so let's say you have this:

Tbl_Test
PK  Field1  Field2

When you are referencing the items in a dataset with code you can use either:

rst.fields("PK")
or
rst.fields(0)

Field position starts at 0 and increments by 1.

[Newby]

Just to be clear, there is no nextfield command to allow FOR Next type coding? Thanks again for your quick reply.

John

you can reference columns in a dataset by either the name of the field or a field position

so let's say you have this:

Tbl_Test
PK  Field1  Field2

When you are referencing the items in a dataset with code you can use either:

rst.fields("PK")
or
rst.fields(0)

Field position starts at 0 and increments by 1.

[rpeare]

Oh you mean if you don't know the number of columns in your query (i.e. from a crosstab query?)

You can do a column count with

rst.fields.count

Then you can cycle through the columns with something like:

for i = 0 to rst.fields.count
VariableName = rst.fields(i)
next i

[Newby]

That is exactly what I looking for. Thanks