1. Home
  2. Point-Of-Sale Software
  3. Importing customer data

Importing customer data

Hi I am trying to import new customers into RMS by means of a linked table in Access. I am having trouble as I keep getting errors that don't make sense (to me):

If I use- Set recCustNew = db.OpenRecordset("dbo_Customer", dbOpenDynaset, dbSeeChanges) recCustNew.AddNew

-I get an 'Invalid Operation' error although exactly the same approach works for adding to the Order table.

If I try to put the new records into an exact copy of Customer within my Access database and then use- db.Execute "INSERT INTO dbo_Customer SELECT * FROM CustomerCopy" I get a 'Cannot update DBTimeStamp';field not updateable error ....which makes me think the problem relates to this particulkar field - which isnt found in the Order table ...

If I try db.Execute "INSERT INTO dbo_Customer SELECT Address2,Title,EmailAddress FROM CustomerCopy" I'm back to the 'Invalid Operation' error

How can this be invalid!!??!!

Reply to
Geoffo2000
Loading thread data ...

Because most of the fields do not allow null values, and when you try to do an insert with only selected fields, you are running into validation errors. To do an INSERT, you need to specify the fields that you will insert into, and provide values for each of those fields. Your final query is relying on default values or allowed nulls for most columns and the RMS database is not going to accept that.

This is from memory, but I believe that the only columns you want to exclude are ID, dbTimeStamp, and possibly the CustomText*, CustomDate* and CustomNumber* fields.

I don't really recommend doing the sort of inserts into an RMS database unless you really know what you are doing. If you are having issues of this type, you should probably steer clear.

Retail Realm has an import utility that will make import tasks much easier and more reliable (years of experience with the RMS database have been programmed in). check it out by contacting your reseller or on the web at

formatting link

Glenn Adams Tiber Creek C> Hi

Reply to
Glenn Adams [MVP - Retail Mgmt

Hi Glenn

Thanks for getting back to me on this. I tried the Insert the way you suggested but it didn't help. To be honest, I don't think it is anything to do with the Insert as ' recCustNew.AddNew' comes up with the same error before any field has been updated-which is the way I would have done it if it hadn't failed. Also, the same approach works for other RMS tables (Order, Order Entry) so why not Customer? I am beginning to think it might actually be something to do with my ODBC driver - if I look in SQL Server Enterprise Manager the key of table Customer is ID (makes sense!) - if I link this table into an Access database the key has somehow become DBTimestamp ... (V.strange)

Is there a better way of getting data into RMS then? ...and I don't mean 'buy someone's add-on'!!!! :-)

Geoff

"Glenn Adams [MVP - Retail Mgmt]" wrote:

Reply to
Geoffo2000

Take your 'CustomerCopy' table and get it into MS Excel.

Use Excel text functions to create a separate INSERT statement for every record.

Backup you database before copying the entire series of INSERT statements into an SO/HQ Administrator query window.

I have done this multiple times with 100% success.

Tom

Reply to
Terrible Tom

Reply to
pizzaboy

BeanSmart website is not affiliated with any of the manufacturers or service providers discussed here. All logos and trade names are the property of their respective owners.