Insert Into Runtime error

  • Thread starter Thread starter LeftyLeo via AccessMonster.com
  • Start date Start date
L

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
 
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]
 
storrboy said:
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]
Click to expand...

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

Thanks anyway
 
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)
 
Douglas said:
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,
Click to expand...
[quoted text clipped - 35 lines]
= tblLocation.fldLocation;", cnn, adOpenDynamic, adLockBatchOptimistic
rec.Close
Click to expand...
Click to expand...

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.
 
LeftyLeo via AccessMonster.com said:
Douglas said:
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,
Click to expand...
[quoted text clipped - 35 lines]
= tblLocation.fldLocation;", cnn, adOpenDynamic, adLockBatchOptimistic
rec.Close
Click to expand...
Click to expand...

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.
Click to expand...

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.
 
Back
Top