Macro Error for connecting with database

[Perch1224]

I am currently trying to make a macro that connects to an oracle database. My problem is I keep getting a runtime error 1004. One of my thoguhts is that the database may not be sending the right information. I am new to writing code in vba so it might be a simple mistake that I am overlooking any help that you are willing to offer would be much appleciated.

Private Sub cmdTest_Click()

'Defining variables
Dim cnOra As ADODB.Connection
Dim rsOra As ADODB.Recordset
Dim db_name As String
Dim UserName As String
Dim Password As String

Set cnOra = New ADODB.Connection
Set rsOra = New ADODB.Recordset

db_name = "AS400"
UserName = "User"
Password = "Userpw"

'Making an ODBC connection according to ADO
cnOra.Open "DSN=" + db_name + ";UID=" + UserName + ";PWD=" _
& Password + ";"
rsOra.CursorLocation = adUseServer

'Running a query
rsOra.Open "File", cnOra, adOpenForwardOnly

'Passing on data from the recordset to a variable or cell.
'Notice that the column name or alias is used to address
'data in the recordset.
While Not rsOra.EOF

Worksheets("Sheet2").Range("A3").End(xlDown).Offset(1, 0) _
= rsOra![ITEMD1]

rsOra.MoveNext
Wend

rsOra.Close
rsOra.Open , cnOra, adOpenForwardOnly

While Not rsOra.EOF
Worksheets("Sheet2").Range("A2") = rsOra![ITEMD1]
rsOra.MoveNext
Wend


rsOra.Close
cnOra.Close
Set rsOra = Nothing

End Sub

 

[Norie]

Where are you getting the error?

 

[Perch1224]

Private Sub cmdTest_Click()

'Defining variables
Dim cnOra As ADODB.Connection
Dim rsOra As ADODB.Recordset
Dim db_name As String
Dim UserName As String
Dim Password As String

Set cnOra = New ADODB.Connection
Set rsOra = New ADODB.Recordset

db_name = "AS400"
UserName = "User"
Password = "Userpw"

'Making an ODBC connection according to ADO
cnOra.Open "DSN=" + db_name + ";UID=" + UserName + ";PWD=" _
& Password + ";"
rsOra.CursorLocation = adUseServer

'Running a query
rsOra.Open "File", cnOra, adOpenForwardOnly

'Passing on data from the recordset to a variable or cell.
'Notice that the column name or alias is used to address
'data in the recordset.
While Not rsOra.EOF

Worksheets("Sheet2").Range("A3").End(xlDown).Offset(1, 0) _
= rsOra![ITEMD1]
rsOra.MoveNext
Wend

rsOra.Close
rsOra.Open , cnOra, adOpenForwardOnly

While Not rsOra.EOF
Worksheets("Sheet2").Range("A2") = rsOra![ITEMD1]
rsOra.MoveNext
Wend


rsOra.Close
cnOra.Close
Set rsOra = Nothing

End Sub

I am getting the error in the highlighted portion it says application defined or object defined error 1004.

 

[Norie]

It's something wrong with

Worksheets("Sheet2").Range("A3").End(xlDown).Offset(1, 0) = "Test"

when I try it exactly as above I get the same error.

I think you may be trying to insert something beyond row 65535.

Check

MsgBox Worksheets("Sheet2").Range("A3").End(xlDown).Row