Insert Into Runtime error

[LeftyLeo via AccessMonster.com]

Hello all,

I have searched with no luck for info on an Invalid Object runtime issue, so
am posting a new thread. Can anyone give me some insight on what could be
the problem with my SQL statement? I am getting a -2147217865 run time error,
Invalid object name 'StoreEntryData'. This name is a correct table name,
though it is not a linked sql table as with dbo.tblMasterTable. Any help
would be greatly appreciated!


Set cnn = New ADODB.Connection
With cnn
.Provider = "Microsoft.Access.OLEDB.10.0"
.Properties("Data Provider").Value = "SQLOLEDB"
.Properties("Data Source").Value = "N701SQL16"
.Properties("Initial Catalog").Value = "Sql db name"
.Properties("User Id").Value = "db Id"
.Properties("Password").Value = "db pw"
.Open
End With
Set rec = New ADODB.Recordset
rec.Open "INSERT INTO dbo.tblMasterTable ( fldDate, fldLocation,
fldPeriod, fldYear, [Beginning Balance], fldOrganization, fldAddress, fldCity,
fldState, fldZip, fldDescription, [Req Letter on File], [501 (c) 3 Number],
[Focus Area], [Donation Description], fldAmount, fldZone )" & _
"SELECT StoreDataEntry.Date, StoreDataEntry.Location, StoreDataEntry.
Period,StoreDataEntry.[Year], StoreDataEntry.[Beginning Balance],
StoreDataEntry.Organization, StoreDataEntry.Address, StoreDataEntry.City,
StoreDataEntry.State, StoreDataEntry.Zip, " & _
"StoreDataEntry.Description, StoreDataEntry.[Req Letter on File],
StoreDataEntry.[501 (c) 3 Number], StoreDataEntry.[Focus Area],
StoreDataEntry.[Donation Description], StoreDataEntry.Amount, tblLocation.
fldZone FROM StoreDataEntry INNER JOIN tblLocation ON StoreDataEntry.Location
= tblLocation.fldLocation;", cnn, adOpenDynamic, adLockBatchOptimistic
rec.Close

 

[storrboy]

At first glance, I think it might be something to do with
"StoreDataEntry.Date". Date is a reserved word (function) in VB. Try
surrounding it with [ ] ie. StoreDataEntry.[Date]

 

[LeftyLeo via AccessMonster.com]

At first glance, I think it might be something to do with
"StoreDataEntry.Date". Date is a reserved word (function) in VB. Try
surrounding it with [ ] ie. StoreDataEntry.[Date]

No, such luck. Still receiving the invalid object error.

Thanks anyway

 

[Douglas J. Steele]

You're missing a space between the first line of your SQL statement and your
second line.

(and you should consider renaming the Date field, or at least putting square
brackets around it, as storrboy suggested)

 

[LeftyLeo via AccessMonster.com]

You're missing a space between the first line of your SQL statement and your
second line.

(and you should consider renaming the Date field, or at least putting square
brackets around it, as storrboy suggested)

Hello all,

[quoted text clipped - 35 lines]

= tblLocation.fldLocation;", cnn, adOpenDynamic, adLockBatchOptimistic
rec.Close

Yes, I surrounded the Date field with brackets [] and placed a space in the
first line of my statement ...fldAmount, fldZone ) " & _ and I am still
getting the error. Thanks for the help.

Is it possible that it may be an issue with the StoreDataEntry table is an
Access table and the dbo.tblMasterTable is a SQL table? I am just not sure.

 

[Douglas J. Steele]

You're missing a space between the first line of your SQL statement and
your
second line.

(and you should consider renaming the Date field, or at least putting
square
brackets around it, as storrboy suggested)

Hello all,

[quoted text clipped - 35 lines]

= tblLocation.fldLocation;", cnn, adOpenDynamic, adLockBatchOptimistic
rec.Close

Yes, I surrounded the Date field with brackets [] and placed a space in
the
first line of my statement ...fldAmount, fldZone ) " & _ and I am still
getting the error. Thanks for the help.

Is it possible that it may be an issue with the StoreDataEntry table is an
Access table and the dbo.tblMasterTable is a SQL table? I am just not
sure.

Looking a little closer, you're trying to open a recordset for an INSERT
INTO statement. Action queries (INSERT INTO, UPDATE, DELETE) don't create
recordsets. Try using the Execute method of the Command object instead.

And yes, one table being Access and the other being SQL will be a problem,
since you've only got a single Connection defined. Unfortunately, I've never
tried doing that using ADO, so I can't offer you a suggestion.