Trying ADO Cmd.Execute first time 1

[timhans]

Good morning, Trying ADO Cmd.Execute for first time, getting error msg run time error '-2147217833(80040e57)' Field to small to accept the amount of data, but runs in GUI as well as Docmd.Run SQL Attempting to update table with SQL statement here is the code I am using, any thoughts, comments or insight in to what I am doing wrong is greatly appreciated.

Private Sub btnRun_DblClick(Cancel As Integer)
Dim Cmd As ADODB.Command
Dim iSQL As String
Dim recs As Long

Set Cmd = New ADODB.Command


iSQL = "INSERT INTO tblCustomersFinished ( CustomerID, FirstName, LastName, Address, Apt, City, State, Phone, Zip, Contact, Physician, DOB, Model, SerialNumber, Insurance, Diagnosis)" & _
"SELECT CustomerID, FirstName, LastName, Address, Apt, City, State, Phone, Zip, Contact, Physician, DOB, Model, SerialNumber, Insurance, Diagnosis FROM " & Me.txtfromz.Value & ""

Set Cmd = New ADODB.Command

Set Cmd.ActiveConnection = CurrentProject.Connection
Cmd.CommandText = iSQL

Cmd.CommandType = adCmdText
Cmd.Execute RecordsAffected:=recs,options:=adExecuteNoRecords 'this is hilighted in debug mode
Set Cmd = Nothing

Exit Sub
ConnectionError:
MsgBox "There was an Error Connecting to the DataBase." & Chr(13) _
& Err.Number & ", " & Err.Description

End Sub

 

[jrbarnett]

You only need one of the

Set Cmd = New ADODB.Command

lines (you have it twice).

What sort of database is this connecting to? is it Access, SQL Server, MySQL etc?

John

 

[timhans]

jrbarnett, thank you for responding, I removed the redundant code but still get the same error,"field to small to accept the amount of data". I am in access 2007, it looks right but I'm new at this. Thanks again

 

[jrbarnett]

What's in the variable txtFromz?

Is is the name of the source table?

Add debug.print iSQL
before the cmd.Execute line and see what it reads.

This way you can see if there are any syntax errors.

John

 

[timhans]

jrbarnett, thanks again for responding, say I managed to get iSQL to run, I changed the field size of every thing in my table and that with other minor things cleared up my error msg. Not sure why that fix it but it did.