Run-time error '3045'

[lovell811]

When I run through this code:

DbN = "\\Green4\MSSQL\DATA\MSSQL\Data\SalesInvent_Data.MDF"

Set PTDB = Workspaces(0).OpenDatabase(DbN)

it gives me "Runtime Error '3045' could not use: "\\Green4\blablabla", file already in use."

But it isn't, what's going on. Is it a problem in the code or with SQL??
----------------
Joe

 

[lovell811]

ANYONE have any suggestions? ----------------
Joe

 

[sunila7]

Hi,

what exactly r u trying to do with this code. r u trying to open a SQL Server DB?

Sunil

 

[lovell811]

I am using visual basic to open up a db and then print a Crystal reports form. I know how to do this except for some reason I get this error whenever I try to open the workspace. ----------------
Joe

 

[lovell811]

can anyone help with this? ----------------
Joe

 

[lovell811]

someone in here has to know the answer to this. ----------------
Joe

 

[tlbroadbent]

You cannot open a SQL MDF in that manner. You need to open an ADO or ODBC connection to SQL Server. SQL Server will read the MDF file. Your code will never reference the MDF. I recommend you review the VB documentation and set up the proper connection. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[syl73]

Hi joe,
could u tell me what version of crystal reports u r using??

Selva.T

 

[lovell811]

I have the right connection, but what I want is to open up a workspace(that is where my problem is). I have a program that was running on access but now I'm changing everything over to SQL server. I need to be able to check and see if a talbes ther, I'm doing this with OpenSchema, if it exists, then delete it and recreate it, if it doesn't exist then just create it. I know how to do most of this except how to be able to create the table. Thanks for the help tlbroadbent. ----------------
Joe

 

[lovell811]

can anyone help me with this? terry? how can I create a table using ado with SQL. I now how with DAO so if it's similar I only need a little help. Thanks. ----------------
Joe

 

[SQLSister]

You could use a stored procedure to check the existence of a table then drop it. I don't know why you want to do this from the user interface, but I would be careful about doing so. Can't you just clear the records from the existing table? Or use a temp table or make the records have a unique identifier so you can tell which one to use? Anyway, here's an example of the code to check for what you want and then create the table. You'll note thet text types are different from Access, you'll want to read up on them in Books on Line before writing your create statement.

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

/****** Object: Table [dbo].[AirportRemarks] Script Date: 7/15/2002 1:41:20 PM ******/
CREATE TABLE [dbo].[AirportRemarks] (
[RecInd] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AirportID] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SiteNumber] [varchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RemElementName] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RemarkText] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RemarkID] [int] IDENTITY (1, 1) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

If you do this you will also want to follow this code with one or more statements granting the appropriate user rights to the table because you lose those when you drop the table.

Use something like:
GRANT SELECT ON AirportRemarks TO WebAccess

of course the actual rights you choose to grant will differ depending on what each role or user needs to have.

You would also have to add code to set up any indexes, primary key, constraints.

 

[lovell811]

I am confused, is this code for SQL or VB, I want VB code. And I know I should be posting there, but I have and no one knows. that is why i put it in the sql forum. I guess I'll just have to figure it out on my own. thanks anyway. ----------------
Joe

 

[sunila7]

Hi,

This is a sample code to connect to a SQL server DB using ADO. Have a look at this, this should help u to start with ADO

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adosql/adoprg01_1kwv.asp

Sunil

 

[lovell811]

thanks everyone, but i figured it out already on my own. thanks for all your input anyway. ----------------
Joe

 

[tlbroadbent]

Joe,

Perhaps you could share your solution. Someone else may have a similar problem or question in the future. Posting your solution here may prove helpful. That is what these forums are all about.

We weren't able to help because you were going about things in an unusual if not impossible manner. I really had to think about it before being able to post my first reply.

Some Access methods and code cannot be directly converted to VB/SQL due to the fact that Access combines VBA, SQL, database adminstration, form and report generation, etc. into one product. Even if code can be converted that doesn't mean a method that works well in Access will work well or should even be used in SQL Server. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.