Access using wrong database

[petermeachem]

I have an Access 2003 front end and sql 2000 holding the data.
We want a test database so I found some code (on tek-tips) to switch between live and test. thread700-676288

This looks just fine, the data looks right, the connection field in msysobjects is correct.

However.
One thing I needed to do was to import some csv files, so I changed from live to test. Then the data was deleted.
currentdb.Execute "DELETE * FROM " & rsCurFile!sTableName
and then new data copied in using TransferText.
This didn't work and I saw that the data was being deleted from the live database and then an attempt was made to append the data to the table in the test database.

Almost as though currentdb is still connected to the live database and not the test one.
How weird is that? If that is what is happeneing, how can I fix it, otherwise what is going on?

 

[petermeachem]

After a bit of investigation, I find that the programme is running the wrong stored procedure.

It does this
currentdb.QueryDefs("xspGGDropTables").Execute
Where xspGGDropTables is
exec xspGGDropTables

and xspGGDropTables is like
CREATE PROCEDURE xspGGDropTables AS

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GGManufacturer]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[GGManufacturer]


CREATE TABLE [dbo].[GGManufacturer] (
[MANUFACTURER_CODE] [varchar] NULL ,
[MANUFACTURER_DESC] [varchar] (50) NULL,
[NUMBER_OF_MODELS] [int] NULL ,
CONSTRAINT [PK_GGManufacturer] PRIMARY KEY CLUSTERED
(
[MANUFACTURER_CODE]
) ON [PRIMARY]
) ON [PRIMARY]
GO

And so forth.
Executing the SP in what should have been the test database actually dropped and recreated the tables in the live one.

I find this totally bemusing. Can anyone please shed some light?

 

[petermeachem]

mm, no replies. That usually means the question is too hard or too easy.

What I eventually found was the way to fix it was to run a stored procedure using a connection string using a dsn for the proper database. This is all dao by the way. I shall attempt to make it connectionless as it is a nuisance adding another dsn for everyone.
While I was at it, I binned the above sp and replaced the code with Truncates.