Manage Triggers grayed out

What do I need to do to give someone access to Manage Triggers in EM?
Is there a way to give them access to view triggers via EM, but disable their ability to create/drop?
You need to be table owner or higher for this. And you cannot grant table owner in 2000. In other
words, either be the owner of the table or db_owner (you could try db_ddladmin, not sure). Whether
EM then will enable the option or not, I don't know...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"JLS" <jlshoop@.hotmail.com> wrote in message news:eoNMiiW5FHA.620@.TK2MSFTNGP12.phx.gbl...
What do I need to do to give someone access to Manage Triggers in EM?
Is there a way to give them access to view triggers via EM, but disable their ability to
create/drop?

Manage Triggers grayed out

What do I need to do to give someone access to Manage Triggers in EM?
Is there a way to give them access to view triggers via EM, but disable thei
r ability to create/drop?You need to be table owner or higher for this. And you cannot grant table ow
ner in 2000. In other
words, either be the owner of the table or db_owner (you could try db_ddladm
in, not sure). Whether
EM then will enable the option or not, I don't know...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"JLS" <jlshoop@.hotmail.com> wrote in message news:eoNMiiW5FHA.620@.TK2MSFTNGP
12.phx.gbl...
What do I need to do to give someone access to Manage Triggers in EM?
Is there a way to give them access to view triggers via EM, but disable thei
r ability to
create/drop?

Malicious User

Not sure if its the right place..but need help cracking this...Just
inherited a bad place......
Users access a certain share point site and browse a directory for a host of
folders.This afternoon one of the folders was deleted which has loads of
subfolders(as it is a sharepoint server).....now I need to find out who thi
s
kool dude is!...
What I have now: System state backup of the Machine,SQL full backup and the
backup(SQL and System) just after the files have
been deleted.
All I have is just Auditing for success and failure but nothing with object
access,didnt think if it would matter even if
object acess was enabled...
now...with the given situation...how do I get to this dude?Can someone
enrich my novice knowledge please?SQL server security...what do i look for
and where?
thanks
maverickHi maverick,
Not sure how Sharepoint works with SQL Server in this case but to find
out what people are using on the SQL Server level you should be using
SQL Profiler to capture a trace of all activity carried out against the
SQL Server. Hope this helps.
Jonathan
maverick wrote:
> Not sure if its the right place..but need help cracking this...Just
> inherited a bad place......
> Users access a certain share point site and browse a directory for a host
of
> folders.This afternoon one of the folders was deleted which has loads of
> subfolders(as it is a sharepoint server).....now I need to find out who t
his
> kool dude is!...
>
> What I have now: System state backup of the Machine,SQL full backup and th
e
> backup(SQL and System) just after the files have
> been deleted.
> All I have is just Auditing for success and failure but nothing with objec
t
> access,didnt think if it would matter even if
> object acess was enabled...
> now...with the given situation...how do I get to this dude?Can someone
> enrich my novice knowledge please?SQL server security...what do i look for
> and where?
>
> thanks
> maverick

making the shift from Access SQL to 'Real' SQL ;-)

for the record, this is my first post and i'm not sure this is the right place to post, but it makes sence to me.

i've been spending a great deal of time with our new MS SQL server and love it but i've noticed there's a fairly large diff between the SQL i'm used to seeing in MS Access and the SQL that the server uses. as you can see by my below statement i'm starting to get the hang of this but i still run into new issues...

my question... what's wrong with my IIf statements? this is what the server spits out...

Msg 156, Level 15, State 1, Line 4

Incorrect syntax near the keyword 'LIKE'.

[ACCPAC_OESHDT].[TERRITORY] is an nvarchar(6) and contains data such as '1', '2', '3', etc...

i've tried many many variants and always have the same outcom. i've tried casting as an int, rtrim to rid the value of any possible white space, expanded versions.

oh, almost forgot... my environment is... I open the SQL Management Studio then logon to SQL Database Engine and then create a New Query.

i'm stuck, please advise. ;-)

SELECT

ACCPAC_OESHDT.YR,

ACCPAC_OESHDT.PERIOD,

IIf([ACCPAC_OESHDT].[TERRITORY] LIKE '[1-4]','Group A',

IIf([ACCPAC_OESHDT].[TERRITORY] LIKE '[5-7]','Group B',

IIf([ACCPAC_OESHDT].[TERRITORY] LIKE '[89]','Group C','_Unknown Territory'))) AS TERRITORY,

SUM(CAST([SAMTSALES] AS MONEY)) AS Sales

FROM ACCPAC_OESHDT

GROUP BY

ACCPAC_OESHDT.YR,

ACCPAC_OESHDT.PERIOD,

IIf([ACCPAC_OESHDT].[TERRITORY] LIKE '[1-4]','Group A',

IIf([ACCPAC_OESHDT].[TERRITORY] LIKE '[5-7]','Group B',

IIf([ACCPAC_OESHDT].[TERRITORY] LIKE '[89]','Group C','_Unknown Territory')))

HAVING ((SUM(CAST([SAMTSALES] AS MONEY)))>0);

Look up the CASE feature in the documentation for a good solution to this problem.

-Ryan

|||

Case when ACCPAC_OESHDT.TERRITORY LIKE '[1-4]' then 'Group A'

when ACCPAC_OESHDT.TERRITORY LIKE '[5-7]' then 'Group B'

when ACCPAC_OESHDT.TERRITORY LIKE '[8-9]' then 'Group C'

else '_Unknown Territory'

end as TERRITORY,

I think that should get you started...

Zep--

|||

thank you VERY much for your guidance... I have been able to make great progress with my queries and am now off to learn how to nest.

Thank you again ;-)

|||

Oh, you're going to be forced to learn all kinds of fun stuff coming from access :)

Zep--

|||

I’m starting to see that… well i hope i'm not going to get labeled... I’ve got a decent amount of experience in other languages such as Perl on WinTel, Mac, and Linux, Postscript, VB, Apple Script and others so I’m not a canned Access dude floating into a new SQL world, hope this is a good thing for me, hehe… thank you very much again for the support… this has been an exciting journey for me and I’m now seeing this platform, MS SQL 2005, is like a whole new world, absolutely massive, wondrous, full of pit holes and wiled ability, at least in my eyes… very exciting ;-)

Making an MSDE DB with Access

Hi
I'm trying to create an MSDE database, and I don't have SQL Server
installed. Do i need to have SQL Server installed to create and run the MSDE
database? I thought the whole idea of MSDE was so you don't need SQL server
to create it or run, while only being limited to DB size and limited
concurrent use.
Can anyone shed light on the situation. I seem to be going round and round
in circles regarding this issue on the Microsoft website.
Thanks
Taniel
Simply put MSDE is a scaled down version of SQL Server. There is a size
limit to the MSDE database size and some of the high end features of SQL
Server are not available to the MSDE users. Other than that MSDE users have
all the functionality of SQL Server at their disposal.
If you have MSDE up and running you do not need SQL Server unless you want
to use restricted high end features.
Gajendra
"Taniel" <Taniel@.discussions.microsoft.com> wrote in message
news:78B9FEAD-D233-4747-BAD8-2224C4E7F690@.microsoft.com...
> Hi
> I'm trying to create an MSDE database, and I don't have SQL Server
> installed. Do i need to have SQL Server installed to create and run the
MSDE
> database? I thought the whole idea of MSDE was so you don't need SQL
server
> to create it or run, while only being limited to DB size and limited
> concurrent use.
> Can anyone shed light on the situation. I seem to be going round and round
> in circles regarding this issue on the Microsoft website.
> Thanks
> Taniel

Making an Access - SQL db solution portable

I have an Access application which runs in a server enviroment where I have
an SQL server that the Access connects to. The customer is so happy with my
soultion and told another company about it, they would like something like
that, so I need to make a copy of the setup to a cd to send to the other
company - as a demo. They have no employees with enough IT knowledge to
install a SQL Server/MSDE and the import the database and setting up Access
to connect to the new server. Remote control is not an option, neither is
"local control" since they will not pay for my trip (transatlantic) unless
they want me to make a soultion for them...
I need to take the solution and make it portable. But I am not sure how to
do it, I need a "downsizing" wizard or something? A solution could be to
make "some kind of installer" which installes a MSDE and loads the data into
it and connects the Access database to the new db server. Probably the best
solution, if there is no easy way to make it portable.
I would asume there are some tools available for this or similar purposes,
but I don't know any...
My concern is also the fact that there are some views in the SQL database
and also a lot of the VBA code in forms and reports has been modified to fit
the SQL, during upsizing some years back... I wouldn't like to re-write the
rewritten code, just for a demo.
I'm pretty sure I'm not the only one who has ever wanted to do this, if you
have done it, please let me know how. I welcome any suggestions. :o)
Thanks in advance
Martin Gregersen
martin@.gregersen.dkHow about a simple mssql backup of the database?
Then they could just import the backup database on their system
Pieter
"Martin Gregersen" <martin@.gregersen.dk> wrote in message
news:eWiKjFUlGHA.2420@.TK2MSFTNGP04.phx.gbl...
>I have an Access application which runs in a server enviroment where I have
>an SQL server that the Access connects to. The customer is so happy with my
>soultion and told another company about it, they would like something like
>that, so I need to make a copy of the setup to a cd to send to the other
>company - as a demo. They have no employees with enough IT knowledge to
>install a SQL Server/MSDE and the import the database and setting up Access
>to connect to the new server. Remote control is not an option, neither is
>"local control" since they will not pay for my trip (transatlantic) unless
>they want me to make a soultion for them...
> I need to take the solution and make it portable. But I am not sure how to
> do it, I need a "downsizing" wizard or something? A solution could be to
> make "some kind of installer" which installes a MSDE and loads the data
> into it and connects the Access database to the new db server. Probably
> the best solution, if there is no easy way to make it portable.
> I would asume there are some tools available for this or similar purposes,
> but I don't know any...
> My concern is also the fact that there are some views in the SQL database
> and also a lot of the VBA code in forms and reports has been modified to
> fit the SQL, during upsizing some years back... I wouldn't like to
> re-write the rewritten code, just for a demo.
> I'm pretty sure I'm not the only one who has ever wanted to do this, if
> you have done it, please let me know how. I welcome any suggestions. :o)
> Thanks in advance
> Martin Gregersen
> martin@.gregersen.dk
>
>|||How about a simple mssql backup of the database?
Then they could just import the backup database on their system
Pieter
"Martin Gregersen" <martin@.gregersen.dk> wrote in message
news:eWiKjFUlGHA.2420@.TK2MSFTNGP04.phx.gbl...
>I have an Access application which runs in a server enviroment where I have
>an SQL server that the Access connects to. The customer is so happy with my
>soultion and told another company about it, they would like something like
>that, so I need to make a copy of the setup to a cd to send to the other
>company - as a demo. They have no employees with enough IT knowledge to
>install a SQL Server/MSDE and the import the database and setting up Access
>to connect to the new server. Remote control is not an option, neither is
>"local control" since they will not pay for my trip (transatlantic) unless
>they want me to make a soultion for them...
> I need to take the solution and make it portable. But I am not sure how to
> do it, I need a "downsizing" wizard or something? A solution could be to
> make "some kind of installer" which installes a MSDE and loads the data
> into it and connects the Access database to the new db server. Probably
> the best solution, if there is no easy way to make it portable.
> I would asume there are some tools available for this or similar purposes,
> but I don't know any...
> My concern is also the fact that there are some views in the SQL database
> and also a lot of the VBA code in forms and reports has been modified to
> fit the SQL, during upsizing some years back... I wouldn't like to
> re-write the rewritten code, just for a demo.
> I'm pretty sure I'm not the only one who has ever wanted to do this, if
> you have done it, please let me know how. I welcome any suggestions. :o)
> Thanks in advance
> Martin Gregersen
> martin@.gregersen.dk
>
>
---
I am using the free version of SPAMfighter for private users.
It has removed 4026 spam emails to date.
Paying users do not have this message in their emails.
Get the free SPAMfighter here: http://www.spamfighter.com/len|||I would say tell them to install MSDE and be done with it. If that's not an
option maybe you could set up a remote demo over the internet? Run it
locally on your computer and let them view it in the web browser using
NetMeeting or whatever.
"Martin Gregersen" <martin@.gregersen.dk> wrote in message
news:eWiKjFUlGHA.2420@.TK2MSFTNGP04.phx.gbl...
>I have an Access application which runs in a server enviroment where I have
>an SQL server that the Access connects to. The customer is so happy with my
>soultion and told another company about it, they would like something like
>that, so I need to make a copy of the setup to a cd to send to the other
>company - as a demo. They have no employees with enough IT knowledge to
>install a SQL Server/MSDE and the import the database and setting up Access
>to connect to the new server. Remote control is not an option, neither is
>"local control" since they will not pay for my trip (transatlantic) unless
>they want me to make a soultion for them...
> I need to take the solution and make it portable. But I am not sure how to
> do it, I need a "downsizing" wizard or something? A solution could be to
> make "some kind of installer" which installes a MSDE and loads the data
> into it and connects the Access database to the new db server. Probably
> the best solution, if there is no easy way to make it portable.
> I would asume there are some tools available for this or similar purposes,
> but I don't know any...
> My concern is also the fact that there are some views in the SQL database
> and also a lot of the VBA code in forms and reports has been modified to
> fit the SQL, during upsizing some years back... I wouldn't like to
> re-write the rewritten code, just for a demo.
> I'm pretty sure I'm not the only one who has ever wanted to do this, if
> you have done it, please let me know how. I welcome any suggestions. :o)
> Thanks in advance
> Martin Gregersen
> martin@.gregersen.dk
>
>|||Hi Martin,
Actually - you can make a setup, which will install MSDE, attach database
and then install all necessary components to run your application - but this
also time consuming task if you never did so.
I think that installing your application on windowsxp and then let them
connect to it with remote desktop it order to try - is most realistic option
for you
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com
"Martin Gregersen" <martin@.gregersen.dk> wrote in message
news:eWiKjFUlGHA.2420@.TK2MSFTNGP04.phx.gbl...
>I have an Access application which runs in a server enviroment where I have
>an SQL server that the Access connects to. The customer is so happy with my
>soultion and told another company about it, they would like something like
>that, so I need to make a copy of the setup to a cd to send to the other
>company - as a demo. They have no employees with enough IT knowledge to
>install a SQL Server/MSDE and the import the database and setting up Access
>to connect to the new server. Remote control is not an option, neither is
>"local control" since they will not pay for my trip (transatlantic) unless
>they want me to make a soultion for them...
> I need to take the solution and make it portable. But I am not sure how to
> do it, I need a "downsizing" wizard or something? A solution could be to
> make "some kind of installer" which installes a MSDE and loads the data
> into it and connects the Access database to the new db server. Probably
> the best solution, if there is no easy way to make it portable.
> I would asume there are some tools available for this or similar purposes,
> but I don't know any...
> My concern is also the fact that there are some views in the SQL database
> and also a lot of the VBA code in forms and reports has been modified to
> fit the SQL, during upsizing some years back... I wouldn't like to
> re-write the rewritten code, just for a demo.
> I'm pretty sure I'm not the only one who has ever wanted to do this, if
> you have done it, please let me know how. I welcome any suggestions. :o)
> Thanks in advance
> Martin Gregersen
> martin@.gregersen.dk
>
>|||Hi Alex
I would like if you could tell me how to make such an installer, then I will
know and can make the best decission. They really need a copy to play with,
not just a demonstration online, they need to take every process of their
production into consideration and then see would features they like/dislike
and so on.
Also I would like to know just to know ;o)
Thanks
Martin
"Alex Dybenko" <alexdyb@.PLEASE.cemi.NO.rssi.SPAM.ru> wrote in message
news:OzFt1nUlGHA.1208@.TK2MSFTNGP02.phx.gbl...
> Hi Martin,
> Actually - you can make a setup, which will install MSDE, attach database
> and then install all necessary components to run your application - but
> this also time consuming task if you never did so.
> I think that installing your application on windowsxp and then let them
> connect to it with remote desktop it order to try - is most realistic
> option for you
>
> --
> Best regards,
> ___________
> Alex Dybenko (MVP)
> http://alexdyb.blogspot.com
> http://www.PointLtd.com
>
> "Martin Gregersen" <martin@.gregersen.dk> wrote in message
> news:eWiKjFUlGHA.2420@.TK2MSFTNGP04.phx.gbl...
>|||Hi Martin,
personally - I use Wise installation system, but you can also try some free
installer, for example see
http://alexdyb.blogspot.com/2006/04...-installer.html
then you can download MSDE setup package from Microsoft site, and include in
your package
now you need to build a script to run MSDE setup, copy database files,
application files, attach database (I use attachDB to do so:
http://www.pointltd.com/Products/Details.asp?dlID=46)
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com
"Martin Gregersen" <martin@.gregersen.dk> wrote in message
news:uEFXGfclGHA.4708@.TK2MSFTNGP04.phx.gbl...
> Hi Alex
> I would like if you could tell me how to make such an installer, then I
> will know and can make the best decission. They really need a copy to play
> with, not just a demonstration online, they need to take every process of
> their production into consideration and then see would features they
> like/dislike and so on.
> Also I would like to know just to know ;o)
> Thanks
> Martin
>
> "Alex Dybenko" <alexdyb@.PLEASE.cemi.NO.rssi.SPAM.ru> wrote in message
> news:OzFt1nUlGHA.1208@.TK2MSFTNGP02.phx.gbl...
>

Making a Table from a Query

I have a Query named qryQuery1 from which I would like to make a Table named tblTable1. I need to do this within a VBA Microsoft Access module.
Can anyone help me with the proper code?
Thank you for any help.
JimCreate table x as
Select columns from table2|||r123456,

Thank you for your response. Unfortunately, I am relatively new to this and do not understand what you mean by "Create Table x as select columns from Table 2". How do these relate to the qryQuery1 and Table1 that I am working with? Would it be too much trouble to ask for the actual code required?

Thank you again for your help.

Jim|||Dim str1 As String
st1 = "Select columns from table 2"
... "Create table x as " & str1|||R123456,

Thanks much. I will try working with your suggestion.

Jim|||USE Northwind
GO

--Uh No

CREATE TABLE myTable99 AS SELECT * FROM Orders
GO

--Uh Yes

SELECT * INTO myTable99 FROM Orders
Go

SELECT * FROM myTable99
GO

DROP TABLE myTable99
GO|||Brett,

Thanks much for the suggestion. Actually I am trying to incorporate this into Microsoft Access VBA code and am coming to the conclusion that I am in the wrong forum. True?

Thanks again for your help.

Jim|||So you're not talking to SQL Server?

Try here

http://www.sqlteam.com/forums/forum.asp?FORUM_ID=3|||tsk, tsk, brett, sending someone elsewhere is definitely not nice when this site has a perfectly good Access forum (http://www.dbforums.com/f84/) itself|||I've never scrolled down that far...I just thought they was dissing Access...|||Thanks again Brett,

I guess I don't know enough to even know if I am "talking to SQL Server". I am just learning VBA for Access and that is where I am writing all my code. I have written a couple of SQL lines like:

SQL = "SELECT strTable1.Field1, strTable2.Field2 . . ."

followed by a line like:

Set qdef = CurrentDb.CreateQueryDef("qryTest", SQL)

But when I get what looks like "pure" SQL", I don't know what to do with it.

Thanks again.

Jim|||Yeah, it looks like your building Query objects on the fly in Access

If you set your variable to my syntax it will create the query that will create a table when it's executed...

But why do that?

Why not create the table ahead of time and just do inserts?|||Brett,

I believe that I need to create them on the fly because my two source tables are User-selected from a collection of existing tables. In addition, new tables may be added to these source tables.

I also let the User select a name for the table to be created that can then be stored along with other previously created tables. The created tables can than be retrieved as different scenarios.

Jim|||Originally posted by jpgalasso
Brett,

I believe that I need to create them on the fly because my two source tables are User-selected from a collection of existing tables. In addition, new tables may be added to these source tables.

I also let the User select a name for the table to be created that can then be stored along with other previously created tables. The created tables can than be retrieved as different scenarios.

Jim
If am not mistaken it is possible to run Make-table query in MS Access:

SELECT oldone.id, oldone.code INTO newone
FROM oldone;|||Originally posted by snail
If am not mistaken it is possible to run Make-table query in MS Access:

SELECT oldone.id, oldone.code INTO newone
FROM oldone;

I seem to need some kind of VBA "Create" or "Execute" statement following the above "SELECT" statement. Do you have any idea what the statement would look like? I tried the following but received an error message saying "Data type conversion error":

Set tbldef = CurrentDb.CreateTableDef("tblTable3", SQL)

Thanks again.

Jim|||Look at the vb help for command.execute

Execute, Requery, and Clear Methods Example (VB)

This example demonstrates the Execute method when run from both a Command object and a Connection object. It also uses the Requery method to retrieve current data in a Recordset, and the Clear method to clear the contents of the Errors collection. (The Errors collection is accessed via the Connection object of the ActiveConnection property of the Recordset.) The ExecuteCommand and PrintOutput procedures are required for this procedure to run.

Public Sub ExecuteX()

Dim strSQLChange As String
Dim strSQLRestore As String
Dim strCnn As String
Dim cnn1 As ADODB.Connection
Dim cmdChange As ADODB.Command
Dim rstTitles As ADODB.Recordset
Dim errLoop As ADODB.Error

' Define two SQL statements to execute as command text.
strSQLChange = "UPDATE Titles SET Type = " & _
"'self_help' WHERE Type = 'psychology'"
strSQLRestore = "UPDATE Titles SET Type = " & _
"'psychology' WHERE Type = 'self_help'"

' Open connection.
strCnn = "Provider=sqloledb;" & _
"Data Source=srv;Initial Catalog=Pubs;User Id=sa;Password=; "
Set cnn1 = New ADODB.Connection
cnn1.Open strCnn

' Create command object.
Set cmdChange = New ADODB.Command
Set cmdChange.ActiveConnection = cnn1
cmdChange.CommandText = strSQLChange

' Open titles table.
Set rstTitles = New ADODB.Recordset
rstTitles.Open "titles", cnn1, , , adCmdTable

' Print report of original data.
Debug.Print _
"Data in Titles table before executing the query"
PrintOutput rstTitles

' Clear extraneous errors from the Errors collection.
cnn1.Errors.Clear

' Call the ExecuteCommand subroutine to execute cmdChange command.
ExecuteCommand cmdChange, rstTitles

' Print report of new data.
Debug.Print _
"Data in Titles table after executing the query"
PrintOutput rstTitles

' Use the Connection object's execute method to
' execute SQL statement to restore data. Trap for
' errors, checking the Errors collection if necessary.
On Error GoTo Err_Execute
cnn1.Execute strSQLRestore, , adExecuteNoRecords
On Error GoTo 0

' Retrieve the current data by requerying the recordset.
rstTitles.Requery

' Print report of restored data.
Debug.Print "Data after executing the query " & _
"to restore the original information"
PrintOutput rstTitles

rstTitles.Close
cnn1.Close

Exit Sub

Err_Execute:

' Notify user of any errors that result from
' executing the query.
If rstTitles.ActiveConnection.Errors.Count >= 0 Then
For Each errLoop In rstTitles.ActiveConnection.Errors
MsgBox "Error number: " & errLoop.Number & vbCr & _
errLoop.Description
Next errLoop
End If
Resume Next

End Sub

Public Sub ExecuteCommand(cmdTemp As ADODB.Command, _
rstTemp As ADODB.Recordset)

Dim errLoop As Error

' Run the specified Command object. Trap for
' errors, checking the Errors collection if necessary.
On Error GoTo Err_Execute
cmdTemp.Execute
On Error GoTo 0

' Retrieve the current data by requerying the recordset.
rstTemp.Requery

Exit Sub

Err_Execute:

' Notify user of any errors that result from
' executing the query.
If rstTemp.ActiveConnection.Errors.Count > 0 Then
For Each errLoop In Errors
MsgBox "Error number: " & errLoop.Number & vbCr & _
errLoop.Description
Next errLoop
End If

Resume Next

End Sub

Public Sub PrintOutput(rstTemp As ADODB.Recordset)

' Enumerate Recordset.
Do While Not rstTemp.EOF
Debug.Print " " & rstTemp!Title & _
", " & rstTemp!Type
rstTemp.MoveNext
Loop

End Sub|||Originally posted by jpgalasso
I seem to need some kind of VBA "Create" or "Execute" statement following the above "SELECT" statement. Do you have any idea what the statement would look like? I tried the following but received an error message saying "Data type conversion error":

Set tbldef = CurrentDb.CreateTableDef("tblTable3", SQL)

Thanks again.

Jim

This works from Access and I guess will work with ADO (execute).

Public Sub DoSQL()
Dim SQL As String

SQL = "SELECT oldone.id, oldone.code INTO newone333 FROM oldone; "
DoCmd.RunSQL SQL
End Sub|||Originally posted by Brett Kaiser
Look at the vb help for command.execute

Thank you very much Brett. This will be extremely helpful for both my existing and future projects.

Jim|||Originally posted by jpgalasso
Thank you very much Brett. This will be extremely helpful for both my existing and future projects.

Jim

For some reason the DoCmd.RunSQL statement, while OK for most SQL statements, does not work with a "SELECT" SQL - I guess it would just make too much sense.

Jim|||What do you mean?|||Originally posted by Brett Kaiser
What do you mean?

Sorry this was in response to "Snail" 's comment who suggested that I try DoCmd.RunSQL with the SQL "SELECT" statement. I do not believe that DoCmd.RunSQL works with the "SELECT" statement.

Jim|||Originally posted by jpgalasso
Sorry this was in response to "Snail" 's comment who suggested that I try DoCmd.RunSQL with the SQL "SELECT" statement. I do not believe that DoCmd.RunSQL works with the "SELECT" statement.

Jim

As you can notice there is 'select *** into from ***'. I tested it in Access modules/new and paste, run - it works.

Public Sub DoSQL()
Dim SQL As String
SQL = "SELECT oldone.id, oldone.code INTO newone333 FROM oldone; "
DoCmd.RunSQL SQL
End Sub|||Snail,

You have my apologies and my thanks. I tried your code and it worked great. Since I am creating a Table from a Query, I do get a warning that I am about to add XXX records to a Table. Do you know how I might be able to turn off the warning message.

Thanks again.

Jim|||I think you want the docmd.RunSQL command.|||Originally posted by blindman
I think you want the docmd.RunSQL command.

blindman,

Thank you. Yes, as Snail also suggested, the docmd.RUNSQL command works fine. The only issue I have is that since I am creating a Table from a Query, I get a warning message that XXX records are about to be added to a Table. Do you know how I might turn off that warning?

Jim|||Originally posted by jpgalasso
blindman,

Thank you. Yes, as Snail also suggested, the docmd.RUNSQL command works fine. The only issue I have is that since I am creating a Table from a Query, I get a warning message that XXX records are about to be added to a Table. Do you know how I might turn off that warning?

Jim

Just add DoCmd.SetWarnings False:

Public Sub DoSQL()
Dim SQL As String
DoCmd.SetWarnings False
SQL = "SELECT oldone.id, oldone.code INTO newone555 FROM oldone; "
DoCmd.RunSQL SQL
End Sub|||Originally posted by snail
Just add DoCmd.SetWarnings False:

Public Sub DoSQL()
Dim SQL As String
DoCmd.SetWarnings False
SQL = "SELECT oldone.id, oldone.code INTO newone555 FROM oldone; "
DoCmd.RunSQL SQL
End Sub

Snail,

Thanks so much. Very, very helpful!!

Jim|||Whoa! Remember to issue a Docmd.setwarnings True at the end of your procedure, otherwise you turn off all the warnings for Access. Change a form and it won't prompt you to save. Run an update query and it won't prompt you to verify. Bad.

As a matter of fact, I often put a macro in my Access applications that just set the setwarnings status to true, so that if my code crashes halfway through a procedure I can easily restore the setting.|||Originally posted by blindman
Whoa! Remember to issue a Docmd.setwarnings True at the end of your procedure, otherwise you turn off all the warnings for Access. Change a form and it won't prompt you to save. Run an update query and it won't prompt you to verify. Bad.

As a matter of fact, I often put a macro in my Access applications that just set the setwarnings status to true, so that if my code crashes halfway through a procedure I can easily restore the setting.

Thanks for the additional tip. Yes, I will reset to True right after I need the "False" setting.

Making a new ms access project

I'm having trouble making a new ms access project.
MSDE is running.
I run the wizard and it asks for a user name and password for the new
database. I put in sa and leave the password blank. Then I get an error
message.
What should I do?
Thanks.
Also, do the tables actually reside on in the sql file and the project
links to them? Where is there a tutorial on this?
Thanks again.
I found out the the Symantec Personal Firewall was blocking this.
Which program do I need to have it allow to have access? Ms Access?
Thanks.
"SAC" <someone@.microsoft.com> wrote in message
news:%23F2BGbG6EHA.3616@.TK2MSFTNGP11.phx.gbl...
> I'm having trouble making a new ms access project.
> MSDE is running.
> I run the wizard and it asks for a user name and password for the new
> database. I put in sa and leave the password blank. Then I get an error
> message.
> What should I do?
> Thanks.
> Also, do the tables actually reside on in the sql file and the project
> links to them? Where is there a tutorial on this?
> Thanks again.
>

Make Table Query

Is there the equivalent of the MS Access "Make Table" query?
--
Thanks in advance,
StevenTry
CREATE TABLE
:-)
>--Original Message--
>Is there the equivalent of the MS Access "Make Table"
query?
>--
>Thanks in advance,
>Steven
>
>.
>||||
| Is there the equivalent of the MS Access "Make Table" query?
|
--
Hi Steven,
Action queries in MS Access can be replicated using SQL Server stored
procedures. You can copy and paste the SQL statement, modify it a bit so it
complies to ANSI and it should work.
Hope this helps,
--
Eric Cárdenas
SQL Server support

make more space available for store proc editing

A suggestion for the Microsoft folks (those who have access to them can
forward?):
when editing stored proc from EM, the screen estate is vastly wasted. Some
of us don't care, some of us do care about this. For those who don't care,
they should not have much to object the idea of making more screen space
accessible. So if you folks in the future could reduce the blank space and
convert it to space displaying code, I am one of those who would applaud.
Quentin> when editing stored proc from EM, the screen estate is vastly wasted.
Some
> of us don't care, some of us do care about this.
Use Query Analyzer. Press F8, you can right-click a stored procedure and
hit Edit (or script to new window as create, if you like to drop/create all
your objects).
You can even run only sections of code from this interface (by highlighting
only the relevant portions), test and debug, and - wow - you aren't stuck in
modal mode, so you can actually look at other stored procedures, other
servers, tables, etc. while you are working on a proc.
Enterprise Manager is not really meant for development, but more for
Enterprise Management, hence the name.
A|||Thanks Aaron.
I'm thinking making more space available for EM stored proc editing does not
hurt anyone and some benefit from it.
Quentin
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:OQdu1L4VDHA.1784@.TK2MSFTNGP10.phx.gbl...
> > when editing stored proc from EM, the screen estate is vastly wasted.
> Some
> > of us don't care, some of us do care about this.
> Use Query Analyzer. Press F8, you can right-click a stored procedure and
> hit Edit (or script to new window as create, if you like to drop/create
all
> your objects).
> You can even run only sections of code from this interface (by
highlighting
> only the relevant portions), test and debug, and - wow - you aren't stuck
in
> modal mode, so you can actually look at other stored procedures, other
> servers, tables, etc. while you are working on a proc.
> Enterprise Manager is not really meant for development, but more for
> Enterprise Management, hence the name.
> A
>|||> I'm thinking making more space available for EM stored proc editing does
not
> hurt anyone and some benefit from it.
I understand, however I'm suggesting a workaround, because you're not going
to see any changes to the client tools in SQL Server 7.0/2000.

Make duplicate db?

I am wanting to be able to take my work home with me, problem is I can access the server from there.

Is it possible to work with a local instance on my laptop, then when completed transfer it to another instance on a different pc?

Meaning, talbes,queries,etc.

Thanks

Davids Learning

You can create a backup of the database and restore it to another machine.|||

does that work with the express version?

or would I be better of using Access?

Davids Learning

|||That works very fine with SQL Express.|||

I need help with this.

How do you backup or restore to a different instance on a different machine?

I am new to SQL Server.

I can from my laptop see the server and access the files and do a backup(I believe the backup ran on the server and not on mine), but how do you get the db to my instance on my laptop?

Thanks

DavidsLearning

|||

Look in Books Online for the usage of RESTORE, and especially the WITH MOVE option.

If you don't have Books Online, download a copy here:

SQL Server 2005 Express Books Online Express Edition
http://msdn2.microsoft.com/en-us/library/ms165706.aspx

|||

I quess this is a Monday.

Is there a book for dummies?

Tried to apply what I read, and I still dont get how to assign a different instance.

Is there a way to make it simplier?

DavidsLearning

|||

You execute the RESTORE operation from the INSTANCE (after connection to that instance) where you want to install the database. (You don't 'assign an a different instance'.)

Some weeks , everyday seems like Monday...

Make Connection to SOAP endpoint without integrated login?

I have a endpoint on our SQL Server and I've written a test .Net app to access it and it all works great. It uses the Integrated Login for Authentication.

My project in the end will have connections made to the endpoint via some custom code on a Non Windows/Linux platform. My concern is that there is no Authentication method that I can use from this client. Integrated is out since it not something you log into. SSL is out since there is no Cert store. Isn't there a way to use the SQL Authentication in a Mixed mode SQL Authentication install?

What are my other options? I need to be able to get data from our SQL server from this custom platform. We looked into using TDS protocol, but would rather not have to license it and develop a client on our platform. We wanted to just generate the HTTP Packets to send and receive the data.

Thank you,

Scott<-

SQL SOAP always requires integrated login first before you can use SQL login. You should try digest it should work from a non-MS platform.

Make a validate rule in a table

Hi

In access i can make a rule
like if i have a Coloumn to date
i can make a rule to say that this fields data
shall be > date

can i do this also in sql and how?

regards

alvin

You can create a Check Constraint on a column in a table. Check Constraints can compare a column's value to other values within the same row or to functions, but cannot compare to other rows.

In your example, are you asking how to compare a column named to_date to one called date? Or are you asking how to compare to_date to the value of the curent date returned from a function? There are some wrinkles with the latter, since a row that passes today, might be invalid tomorrow. The constraint will be re-evaluated if the row is modified, even if it is not the to_date field that is changed.

Constraints can be defined in the Create Table statement:

Create Table myTable(

from_date datetime Check( from_date < GetDate() ), -- Unnamed constraint using a function

to_date datetime

Constraint Range_Check Check( to_date > from_date ) -- Named table constraint comparing two columns

)

You can add Constraints with Alter Table as well. See the Books-online for more information.

Alter Table myTable Add Constraint Min_Range Check( DateDiff( dd, from_date, to_date ) > 10 )

make a report against an InSQL database (was "Question.....")

I am new to the database world and I am trying to understand how all this works. I understand Access and SQL for the most part. My idea that I want to implement is that I want to be able to implement a maintenance log that records motor running life, and piston cycles in the plant. I have a PLC communicating to a server to an InSQL database. Where do i start if i want to make a report that basically counts the total hours that a motor is running, or how many times a piston goes in and out?

any help would be appreciated.InSQL? IndustrialSQL Server is an extension to Microsoft® SQL Server™ 7.0, acquiring plant data at dramatically increased speeds, reducing data storage volumes, and integrating plant data with event, summary, production and configuration data. The FactorySuite™ database server leverages the open and flexible Microsoft platform to truly integrate the office with the factory floor. Production personnel can use Wonderware tools or hundreds of ‘off the shelf’ software packages to view, analyze and report on their production process.if this is what you've got, you should be able to use ordinary SQL Server queries in any of a number of front-end tools

hey, you could even use Access, by linking to your InSQL tables|||So your saying that the data I am looking for is already being stored in the SQL database?|||not saying, guessing ;)

is that, in fact, the product you are using? then yes|||Yes, this is the product I am using. It comes with a program calle d"Activefactory" that has a number of programs for trending, querying, and creating reports. I guess i could just start there.

Make a folder available to everyone

What would be the easiest way to make all the reports in a
RS folder available to everybody who can access the Report
Server website, whether or not they have NT accounts?
JoeThere is not an easy and supported way to do this. You could turn on
anonymous access and give rights to the anonymous user, but then you
essentially have no security in your system. You could also look into
writing a custom auth dll that would provide such access, however this is
not trivial.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Joe Bourne" <anonymous@.discussions.microsoft.com> wrote in message
news:7b1701c476ab$b6cb52c0$a501280a@.phx.gbl...
> What would be the easiest way to make all the reports in a
> RS folder available to everybody who can access the Report
> Server website, whether or not they have NT accounts?
> Joe|||Any guidance on msdn library on how to write one of these custom auth dll's?
"Daniel Reib [MSFT]" wrote:
> There is not an easy and supported way to do this. You could turn on
> anonymous access and give rights to the anonymous user, but then you
> essentially have no security in your system. You could also look into
> writing a custom auth dll that would provide such access, however this is
> not trivial.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Joe Bourne" <anonymous@.discussions.microsoft.com> wrote in message
> news:7b1701c476ab$b6cb52c0$a501280a@.phx.gbl...
> > What would be the easiest way to make all the reports in a
> > RS folder available to everybody who can access the Report
> > Server website, whether or not they have NT accounts?
> >
> > Joe
>
>|||Try this link.
http://msdn.microsoft.com/library/?url=/library/en-us/dnsql2k/html/ufairs.asp?frame=true
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Brian Parker" <BrianParker@.discussions.microsoft.com> wrote in message
news:E0A776B4-029B-4070-A5BA-F6821F264F11@.microsoft.com...
> Any guidance on msdn library on how to write one of these custom auth
dll's?
> "Daniel Reib [MSFT]" wrote:
> > There is not an easy and supported way to do this. You could turn on
> > anonymous access and give rights to the anonymous user, but then you
> > essentially have no security in your system. You could also look into
> > writing a custom auth dll that would provide such access, however this
is
> > not trivial.
> >
> > --
> > -Daniel
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> >
> > "Joe Bourne" <anonymous@.discussions.microsoft.com> wrote in message
> > news:7b1701c476ab$b6cb52c0$a501280a@.phx.gbl...
> > > What would be the easiest way to make all the reports in a
> > > RS folder available to everybody who can access the Report
> > > Server website, whether or not they have NT accounts?
> > >
> > > Joe
> >
> >
> >

Maintenance suggestions

Hi,
I recently upgraded a help desk ticket tracking system from Access to MSDE.
I was wondering if there are any suggestions I should consider about
maintaining a database within MSDE?
Thanks in Advance,
JeffH
hi,
JeffH wrote:
> Hi,
> I recently upgraded a help desk ticket tracking system from Access to
> MSDE. I was wondering if there are any suggestions I should consider
> about maintaining a database within MSDE?
start reading at
http://msdn.microsoft.com/library/de...kprst_63eh.asp
...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply