MSDE and Access 2000

Hi everyone,

In using the SQL Server Database Wizard in Access 2000 to create a new
access project, I get two consecutive error messages and then the wizard
shuts down. The first, which apprears immediately after starting the wizard
is a message box stating something like "the property value is too large".
After clicking through and filling out the values in the connection and
database dialog, I get a second dialog box with the message "Overflow" and
the wizard terminates.

My operating system is Windows 2000 Professional with Access 2000 and MSDE
2000.

Does anyone have any thoughts about what might be causing these errors?

Thanks in advance.Hi Stephen

Have a look at Microsoft Knowledge Base Article Q272384
http://support.microsoft.com/defaul...kb;EN-US;272384

It describes the overflow error when creating a MS Access project on MSDE
2000.
Apparently, installing MS Office Service Pack 3 should fix the problem.

Hope this helps!

Martin

"Stephen Bishop" <sbishop33@.comcast.net> wrote in message
news:5KednfNSnerNM-vdRVn-tw@.comcast.com...
> Hi everyone,
> In using the SQL Server Database Wizard in Access 2000 to create a new
> access project, I get two consecutive error messages and then the wizard
> shuts down. The first, which apprears immediately after starting the
wizard
> is a message box stating something like "the property value is too large".
> After clicking through and filling out the values in the connection and
> database dialog, I get a second dialog box with the message "Overflow" and
> the wizard terminates.
> My operating system is Windows 2000 Professional with Access 2000 and MSDE
> 2000.
> Does anyone have any thoughts about what might be causing these errors?
> Thanks in advance.|||Thanks, Martin. I'll take a look at the article and see if it helps. As
far as the office version, I'm using Access 2000 (9.0.2720) and Office
2000, but I don't know the service pack status.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Open Access -> Help -> About Microsoft Access

At the end of first line it should say e.g. SP-1, SP-2 etc depending
on what service pack is installed.

Stephen Bishop <sbishop33@.comcast.net> wrote in message news:<40784f48$0$206$75868355@.news.frii.net>...
> Thanks, Martin. I'll take a look at the article and see if it helps. As
> far as the office version, I'm using Access 2000 (9.0.2720) and Office
> 2000, but I don't know the service pack status.

MSDE 2000A inner join problems

Hi All
I am using VB6 and MSDE 2000
I previously used Access 2000 and used the following ado sql to create a
temp table in access
(Invoice table contains Invoice data such as Invoice number, date etc.
Idetail table contains details of the invoice individual items e.g product
code, Invoice Number etc) The link is the Invoice Number field in each table
(Master/Detail)
sql = "SELECT idetail.qty, idetail.prod_code, invoice.inv_date,
idetail.price INTO idetailtemp IN '" & App.path & "\data\tramcarstemp.mdb'
FROM idetail inner join [invoice] on idetail.inv_num = invoice.inv_num where
invoice.inv_date between #" & Format(DT1.Value, "m-d-yyyy") & "# and #" &
Format(DT2.Value, "m-d-yyyy") & "#"
cn.execute sql
This worked fine in Access 2000
I am upgrading to MSDE2000 and using the following code in TSql
Private Sub maketable()
Dim a, fso As New FileSystemObject
Dim x As Integer
Set a = fso.CreateTextFile(App.path & "\sql\BackupTramcars.sql", True)
a.WriteLine ("EXEC sp_dboption 'Tramcars', 'select into/bulkcopy', 'true'")
a.WriteLine ("GO")
a.WriteLine ("USE " & "Tramcars")
a.WriteLine ("SELECT idetail.qty, idetail.prod_code, invoice.inv_date,
idetail.price INTO idetailtemp")
a.WriteLine ("FROM idetail inner join [invoice] on idetail.inv_num =
invoice.inv_num")
a.WriteLine ("where invoice.inv_date between '" &
Format(frmorderhistory.DT1.Value, "m-d-yyyy") & "' and '" &
Format(frmorderhistory.DT2.Value, "m-d-yyyy") & "'")
a.WriteLine ("GO")
a.WriteLine ("EXEC sp_dboption 'Tramcars', 'select into/bulkcopy', 'false'")
a.WriteLine ("GO")
a.Close
Set a = Nothing
End Sub
The table is created but the inserted invoice dates are only ever 1 of 2
dates
Also when I read from the table to graph the data with
sql = "Select inv_date, sum(qty) as amount from [idetailtemp] where
[prod_code] = '" &
frmorderhistory.maingrid.TextMatrix(frmorderhistor y.maingrid.Row, 0) & "'
group by [inv_date]"
rs.Open sql, cn, adOpenDynamic, adLockReadOnly
There are several records in the recordset but calling rs.recordcount = -1,
which didn't happen in Access 2K
Any ideas
Steve
hi Steve,
steve wrote:
> Hi All
> ...
> Private Sub maketable()
> Dim a, fso As New FileSystemObject
> Dim x As Integer
> Set a = fso.CreateTextFile(App.path & "\sql\BackupTramcars.sql", True)
> a.WriteLine ("EXEC sp_dboption 'Tramcars', 'select into/bulkcopy',
> 'true'") a.WriteLine ("GO")
> a.WriteLine ("USE " & "Tramcars")
> a.WriteLine ("SELECT idetail.qty, idetail.prod_code, invoice.inv_date,
> idetail.price INTO idetailtemp")
> a.WriteLine ("FROM idetail inner join [invoice] on idetail.inv_num =
> invoice.inv_num")
> a.WriteLine ("where invoice.inv_date between '" &
> Format(frmorderhistory.DT1.Value, "m-d-yyyy") & "' and '" &
> Format(frmorderhistory.DT2.Value, "m-d-yyyy") & "'")
> a.WriteLine ("GO")
> a.WriteLine ("EXEC sp_dboption 'Tramcars', 'select into/bulkcopy',
> 'false'") a.WriteLine ("GO")
> a.Close
>
> The table is created but the inserted invoice dates are only ever 1
> of 2 dates
please use the ISO format when referencing dates... dates can be confusing,
but if you refer to them in the 'YYYYMMDD' or 'YYYY-MM-DD' format you will
be out of troubles.. please have a look at
http://www.windowsitpro.com/SQLServe...9147/9147.html ,
http://www.karaszi.com/SQLServer/info_datetime.asp for further info..

> Also when I read from the table to graph the data with
> sql = "Select inv_date, sum(qty) as amount from [idetailtemp] where
> [prod_code] = '" &
> frmorderhistory.maingrid.TextMatrix(frmorderhistor y.maingrid.Row, 0)
> & "' group by [inv_date]"
> rs.Open sql, cn, adOpenDynamic, adLockReadOnly
> There are several records in the recordset but calling rs.recordcount
> = -1, which didn't happen in Access 2K
this depends on the MDAC recordset type... the recordcount value will be
available when the recordset has been fully populated, that's to say as soon
as you perform a .MoveLast operation.. (BTW... .Movelast can be time
consuming as all rows must be fetched and transferred)..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Andrea
Thanks. Worked a treat
Steve
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:3k76maFt35b8U1@.individual.net...
> hi Steve,
> steve wrote:
> please use the ISO format when referencing dates... dates can be
> confusing, but if you refer to them in the 'YYYYMMDD' or 'YYYY-MM-DD'
> format you will be out of troubles.. please have a look at
> http://www.windowsitpro.com/SQLServe...9147/9147.html ,
> http://www.karaszi.com/SQLServer/info_datetime.asp for further info..
>
> this depends on the MDAC recordset type... the recordcount value will be
> available when the recordset has been fully populated, that's to say as
> soon as you perform a .MoveLast operation.. (BTW... .Movelast can be time
> consuming as all rows must be fetched and transferred)..
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>

msde 2000 odbc connection failed

i have msde 2000 running on a server i am able to create an odbc connection to my db on that pc. when i try to create anpther odbc connection on another pc on that network i get the following error:

sql state '01000'
server error 2
[microsoft][odbc sql server driver][named pipes] connectionopen (connect()).
connection failed
sql state '08001'
server error 17
[microsoft][odbc sql server driver][named pipes]server does not exist or access denied.

what do i have to do to get this odbc connection on this pc working?

Thank You,
ThomasHave you enabled network protocols? If not try this. Easier to do this by uninstalling and then reinstalling.|||True, and take help of this KBA http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q328306 to overcome the issue.

MSDE 2000 and merge modules

I have a vb.net desktop application that uses msde 2000 for its databasing. I am trying to create a setup file using the output of my desktop application and the msde 2000 merge modules in order to install my own build plus an instance of msde 2000.

The setup runs properly and then asks me to restart my machine. After the restart, however, there is no such instance running on my machine. I am adding the following internal properties to my msi file using ocra:

SQLMSDESelected 1
SqlInstanceName Midas
SqlSaPwd Password1
SqlSecurityMode SQL
SqlDataDir C:\Program Files\Microsoft SQL Server\MSSQL$Midas\Data
SqlProgramDir C:\Program Files\Microsoft SQL Server
This should create an instance of msde on my machine but when I try to connect using enterprize manager or query analizer (I connect to <<machinename>>\Midas), then I get the standard can not connect error.

I am at my witt's end and would really appreciate any help.

Thanks.

Hi,

maybe you could help me....

I'm trying to create a setup file using the VS2003 "Setup Project" and MSDE2000SP4 Merge Modules (editing properties with ORCA).

But when I run the installation file, it shows the following message:

"The Error Code is 2920".

Do you know how to fix that ?

Thanks,

Jo?o Paulo.

sql

MSDE 2000 and merge modules

I have a vb.net desktop application that uses msde 2000 for its databasing. I am trying to create a setup file using the output of my desktop application and the msde 2000 merge modules in order to install my own build plus an instance of msde 2000.

The setup runs properly and then asks me to restart my machine. After the restart, however, there is no such instance running on my machine. I am adding the following internal properties to my msi file using ocra:

SQLMSDESelected 1
SqlInstanceName Midas
SqlSaPwd Password1
SqlSecurityMode SQL
SqlDataDir C:\Program Files\Microsoft SQL Server\MSSQL$Midas\Data
SqlProgramDir C:\Program Files\Microsoft SQL Server
This should create an instance of msde on my machine but when I try to connect using enterprize manager or query analizer (I connect to <<machinename>>\Midas), then I get the standard can not connect error.

I am at my witt's end and would really appreciate any help.

Thanks.

Hi,

maybe you could help me....

I'm trying to create a setup file using the VS2003 "Setup Project" and MSDE2000SP4 Merge Modules (editing properties with ORCA).

But when I run the installation file, it shows the following message:

"The Error Code is 2920".

Do you know how to fix that ?

Thanks,

Jo?o Paulo.

MSDE (MSSqlSrv 7.0) and trigger

Hello,

i got a msde database (core based on ms sql server 7.0) and want to create a trigger.

Unfortunetaly, msde doesn't know before trigger.
As far as i know, i can access the new values in an update trigger by selecting it from the 'inserted' table. Is there any possibility to access the values in the database before the update action happens ? or..
How can i write a trigger that checks a state column and admit updates only to state = 8 if the old state value was 6 ?

thanks in advance.

dirk

example.. it doesn't work :(

CREATE TRIGGER trigger_order_update on Auftrag for update
AS
BEGIN
declare @.state int
set @.state=(select Status from inserted)
declare @.notation int
set @.notation = (select Bezeichnung from inserted)
if (@.state = 8 AND (select Status from Auftrag where Bezeichnung=@.notation)!=6)
begin
raiserror('trigger trigger_order_update fired',16,1)
rollback
end
end;Originally posted by SeattleDirk
Hello,

i got a msde database (core based on ms sql server 7.0) and want to create a trigger.

Unfortunetaly, msde doesn't know before trigger.
As far as i know, i can access the new values in an update trigger by selecting it from the 'inserted' table. Is there any possibility to access the values in the database before the update action happens ? or..
How can i write a trigger that checks a state column and admit updates only to state = 8 if the old state value was 6 ?

thanks in advance.

dirk

example.. it doesn't work :(

CREATE TRIGGER trigger_order_update on Auftrag for update
AS
BEGIN
declare @.state int
set @.state=(select Status from inserted)
declare @.notation int
set @.notation = (select Bezeichnung from inserted)
if (@.state = 8 AND (select Status from Auftrag where Bezeichnung=@.notation)!=6)
begin
raiserror('trigger trigger_order_update fired',16,1)
rollback
end
end;

I hope you have id or primary key in your table(let say id is unique).
It needs to remember that you can update more than 1 record(may be not from your application - but it is better assume this anyway).
I did not test this - but it has to work.

CREATE TRIGGER trigger_order_update on Auftrag for update
AS
BEGIN

if exists(select 1 from deleted d
join inserted i on i.id=d.id
where d.status=6 and i.status<>8)
begin
raiserror('trigger trigger_order_update fired',16,1)
rollback
end

MSDE - should i use filegroups

To allow for a proper migration path to SQL Server should i create my MSDE db
with FileGroups or can this be done later when migrating to SQL Server
without causing issues?
Can anyone recommend a best practice on what should be in each group?
tables (this can be broken further for large/small tables)
indexs
?
With a 2GB limit on data size I don't believe file groups would be needed.
You can create and remove file groups at any time if you migration path
requires it.
Jim
"Keeper" <Keeper@.discussions.microsoft.com> wrote in message
news:3B5BB1BC-E786-425F-AC9B-FB5778083927@.microsoft.com...
> To allow for a proper migration path to SQL Server should i create my MSDE
> db
> with FileGroups or can this be done later when migrating to SQL Server
> without causing issues?
> Can anyone recommend a best practice on what should be in each group?
> tables (this can be broken further for large/small tables)
> indexs
> ?
|||Since some clients will be msde and some sql server my company would like me
to keep things consistent. Can you point me in the right direction on how to
create the filegroups on msde.
I was thinking about just splitting up the indexes and tables at this point.
"Jim Young" wrote:

> With a 2GB limit on data size I don't believe file groups would be needed.
> You can create and remove file groups at any time if you migration path
> requires it.
> Jim
> "Keeper" <Keeper@.discussions.microsoft.com> wrote in message
> news:3B5BB1BC-E786-425F-AC9B-FB5778083927@.microsoft.com...
>
>

MSDE - Grant Permission?

Hi all,
If I would like to create a user with a db_owner permission & some permissions such as sp_addlogin, sp_adduser etc.
How can I do? Would you mind to give me some examples?
Many thanks.Hi all,

If I would like to create a user with a db_owner permission & some permissions such as sp_addlogin, sp_adduser etc.

How can I do? Would you mind to give me some examples?

Many thanks.

Check sp_addrolemember , Grant, sp_addlogin in Bol its been explained very well with good examples.|||Yes, I used sp_addrolemember, grant , etc.

The following are my steps:
1. use "sa" login to "master"
2. exec sp_addlogin "abc", "abc", "master", null, null, null
go
3. exec sp_adduser "abc", "abc1", "db_owner"
4. exec sp_addrolemember "db_accessadmin" , "abc1"
go
5. exec sp_addrolemember "db_securityadmin" , "abc1"
go
6. exec sp_addrolemember "db_ddladmin" , "abc1"
go
7. exec sp_addrolemember "db_backupoperator" , "abc1"
go
8. exec sp_addrolemember "db_datareader" , "abc1"
go
9. exec sp_addrolemember "db_datawriter" , "abc1"
go
9.5. grant all on sp_addlogin to abc1
go
10. use "abc" login into "master"
11. exec sp_addlogin "bb", "bbpassword", "master", null, null, null
go

Msg 15247, Level 16, State 1, Server MIS-TOMMY\AIAB, Procedure sp_addlogin, Line
17
User does not have permission to perform this action.

How can I solve this?

Many thanks.|||Yes, I used sp_addrolemember, grant , etc.

The following are my steps:
1. use "sa" login to "master"
2. exec sp_addlogin "abc", "abc", "master", null, null, null
go
3. exec sp_adduser "abc", "abc1", "db_owner"
4. exec sp_addrolemember "db_accessadmin" , "abc1"
go
5. exec sp_addrolemember "db_securityadmin" , "abc1"
go
6. exec sp_addrolemember "db_ddladmin" , "abc1"
go
7. exec sp_addrolemember "db_backupoperator" , "abc1"
go
8. exec sp_addrolemember "db_datareader" , "abc1"
go
9. exec sp_addrolemember "db_datawriter" , "abc1"
go
9.5. grant all on sp_addlogin to abc1
go
10. use "abc" login into "master"
11. exec sp_addlogin "bb", "bbpassword", "master", null, null, null
go

Msg 15247, Level 16, State 1, Server MIS-TOMMY\AIAB, Procedure sp_addlogin, Line
17
User does not have permission to perform this action.

How can I solve this?

Many thanks.

Just add two lines before line 10
exec sp_addsrvrolemember N'abc', sysadmin
go
EXEC sp_grantdbaccess N'abc', N'abc1'
go

I hope now it would be ok.
Joydeep :cool:

MSDE

I installed MSDE 2000 in my system. There is no icon & gui screen. How can i create/alter database using MSDE without using any programming language? from where can i get stored data in msde? How can i take backup for this data?See this thread for some ideas on how to manage MSDE:http://forums.asp.net/658936/showpost.aspx
|||

tmorton wrote:

See this thread for some ideas on how to manage MSDE:http://forums.asp.net/658936/showpost.aspx

Thank you very much