Database

Access 2007 , VBA , recordset, adding New field [modified]

7-Feb-11 12:26

This is not a valid use for a function, a stored proc maybe but not a function. There is no way that emp and offices are going to be union compatible, if they are then why are they in different tables. You need to rethink your design.

Select * from anytable

This is frowned upon, explicitly declare the fields.

Never underestimate the power of human stupidity
RAH

scorp_scorp6-Feb-11 19:33

scorp_scorp

6-Feb-11 19:33

Dear ALl,

i have a record set lets say: set rs = db.openrecordset ("SELECT Name, Location FROM tb1")
I need to add a new field "salary" to rs, so i used : rs.fields.append "salary" , adinteger
salary is of integer datatype

This is not working, it is giving me Typemismatch Error and it highlights "salary", i also googled, all says the same method to append, but still not working .....

AM not sure whats the error, or why , Any one can guide how to append this coloumn to the recordset ??? any work around ??? Confused | :confused:

Note: I want to fill the values of this "salary" field with numbers from calculations.

Regards,

0 will always beats the 1.

modified on Monday, February 7, 2011 5:48 AM

Re: Access 2007 , VBA , recordset, adding New field

Eddy Vluggen7-Feb-11 7:24

Eddy Vluggen

7-Feb-11 7:24

scorp_scorp wrote:
any work around ???

How about adding a dummy-column in the select-statement? Something like below;

VB.NET

set rs = db.openrecordset ("SELECT Name, Location, 0 As [Salary] FROM tb1")

You could then overwrite the values in the new column as required Smile | :)

I are Troll Suspicious | :suss:

Re: Access 2007 , VBA , recordset, adding New field [modified]

scorp_scorp7-Feb-11 17:25

scorp_scorp

7-Feb-11 17:25

Greaaaat, thanks alot, this worked excellent but hence i got another problem:

when now i want to fill this feild (after creation), am doing:

With rs
.AddNew
.Fields("salary") = x
.Update
End With

where x is an integer from some where, i have the following error: Field cannot be updated .
i also used :
rs.Edit
rs("salary").Value = x
rs.Update
also same error

Any hint why this error showing up ??? is it the right way to fill in this field ?? i think it is Confused | :confused:

0 will always beats the 1.

modified on Monday, February 7, 2011 11:36 PM

Re: Access 2007 , VBA , recordset, adding New field

Eddy Vluggen8-Feb-11 7:17

Eddy Vluggen

8-Feb-11 7:17

scorp_scorp wrote:
Any hint why this error showing up ???

I don't know, would have to look it up. My guess is that the recordset-object is readonly.

What are you trying to achieve, and in what language? If you need to update some records in a database, you'd best execute the Sql against the database-server Smile | :)

I are Troll Suspicious | :suss:

Re: Access 2007 , VBA , recordset, adding New field

scorp_scorp8-Feb-11 16:51

scorp_scorp

8-Feb-11 16:51

Thaks eddy, i found that recordsets are read only by default ... and also are not modifiable if sql depends on two tables of one-to-many relation or invlove sumation of rows ...

Also, if above conditions are not true, then a recordset to be modifiable, i think attribs should be set like:

set st = db.openrecordset("SQL",2,0,3)
or
set st = db.openrecordset("SQL",adOpenDynamic,adUseNone, adLockOptimistic)

0 will always beats the 1.

pivoting more than one column in sql server

chencoh6-Feb-11 9:24

chencoh

6-Feb-11 9:24

hi everyone,
i have sql server db with a table which contains a field for a workplace and the number of people employed in a specific month and year separated by gender.
it kinda looks like :

workplace month year male female
Microsoft feb 2011 5 1
Google feb 2011 6 2
Intel feb 2011 7 3
Microsoft march 2011 8 4

obviously with different actual data.
is there a way to concatenate the year and month columns and pivoting the data so it'll look something like :

workplace date male female date male female
Microsoft feb 2011 5 1 march 2011 8 4
Google feb 2011 6 2
Intel feb 2011 7 3

sorry if that was a bit too much, I'll be happy to explain myself more clearly if needed.

dan!sh 6-Feb-11 10:14

dan!sh

6-Feb-11 10:14

Select columnA + columnB from someTable

This concatenates the column vakues. Have you tried using this in your pivot query?

"Your code will never work, Luc's always will.", Richard MacCutchan[^]

Mycroft Holmes6-Feb-11 12:02

6-Feb-11 12:02

There are some excellent articles [^]around on that subject.

Never underestimate the power of human stupidity
RAH

List of common misconceptions

Jörgen Andersson6-Feb-11 22:13

Jörgen Andersson

6-Feb-11 22:13

Mycroft Holmes wrote:
There are some excellent articles

Shameless plug, excellent. Big Grin | :-D

5 for that.
And it is a very useful article.

chencoh11-Feb-11 6:43

chencoh

11-Feb-11 6:43

Hey,
Sorry for the late response, I wasn't around a computer.
While article you added really shed some light about the topic, I still have a small problem.
the article showed how to concatenate to columns into one and then pivot the column, what I want to do is to pivot them both.
So if i refer to the article, instead of having a column for "1996-F" and one for "1996-M" I want two columns, one for "M" and one for "F" and "1996" wrapping them.
something like:
1996
M F
6 5

Mycroft Holmes11-Feb-11 12:06

SQL Server Update New Column

11-Feb-11 12:06

Now you are trying to put UI/presentation logic into the database operation. Adding the additional row with the year is not a database operation it needs to be done in your application.

Never underestimate the power of human stupidity
RAH

Member 46502875-Feb-11 6:43

Member 4650287

5-Feb-11 6:43

Dears,

I have SQL Server table which includes contains follwing columns

File Name Telephone

MemeberName_TelephoneNumber_IDNUmber ,TelephoneNumber

I need to add one or more column named ID Number and get the value from column Filename.what is the query to get the result?

please help me

Re: SQL Server Update New Column

R. Giskard Reventlov5-Feb-11 7:27

R. Giskard Reventlov

5-Feb-11 7:27

Not entirely clear what you are asking but this might set you in the right direction:

declare @Id int
set @Id = 999
select FileName from [table] where IDNumber = @Id

Hoep that helps.

"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
nils illegitimus carborundum

me, me, me

Best Security For Small Database

JessStuart3-Feb-11 16:22

JessStuart

3-Feb-11 16:22

Howdy!

I'm creating an application to keep track of people at an event. The app will run on a Windows 7 laptop, and have no network connectivity (I believe that means it's single tier?). The app is going to be written in C# 2008 Express, with the database in SQL Server 2008 Express.

Here's the security concerns for the app:
About 8 people (door volunteers) will use the app in a night, to register dancers when they arrive.
Every half hour, the volunteer changes.
The volunteers are highly computer challenged (or must be assumed to be so).

I don't want a windows login for each volunteer.
The app will prompt for the volunteer to "log in" to the app, as their shift starts.

I'm considering a single windows user for the whole app. The volunteer app "login" will just be a record of whose at the door at a giving time.

Here's my question:
Is the best way to set up the database security to create the app user in windows, or to use SQL Authentication from within the program? What considerations indicate the best choice?

Thanks,

Jess

Re: Best Security For Small Database

PIEBALDconsult4-Feb-11 2:04

PIEBALDconsult

4-Feb-11 2:04

That all sounds reasonable.

A concern I see is the amount of time spent changing users. If there's a line of dancers waiting to be checked in and out, you may not want to wait for Windows to log out and back in and restart the app. So I would have users as a feature of the application. A supervisor-type user can add and remove users. And a user would log into the app by menu or drop-down -- you may or may not ask for a password.

So, I would have a User table which contains the details of each user. The ID of the user can then be attached to the various actions (check-in, check-out) taken within the system.

Re: Best Security For Small Database

Mycroft Holmes6-Feb-11 13:13

Re: Best Security For Small Database

6-Feb-11 13:13

Do NOT use windows logon within the database security. Create a "functional" id in sql server with a usedid and password, have the application use this ID as the credentials to connect to the database.

Manage the user security within the application rather than use windows/sql security. As Piebal suggested have a used table with credentials that can be managed from within your application by an administrator (you presumably)

Never underestimate the power of human stupidity
RAH

_Maxxx_8-Feb-11 16:44

_Maxxx_

8-Feb-11 16:44

Q. Why do you need each volunteer to be separately logged in? Do you need to keep track of who let in whom?

Assuming you're not too concerned about someone trying to steal or knacker the database then the type of security doesn't really matter - integrated or not that is.

You could do something as simple as asking them to type in their name (selection from a drop down could be risky - what if someone volunteers in another user's place?) and then log that in the database.

Disable the entry of the name on a login/logout button and bob's your uncle.

___________________________________________
.\\axxx
(That's an 'M')

update table

Elham M2-Feb-11 5:25

Elham M

2-Feb-11 5:25

How can I update my table every hour?please help me

David Mujica2-Feb-11 5:42

David Mujica

2-Feb-11 5:42

You question is a bit open-ended, but I'll try to point you in the right direction.

If you are logged in as "sa" in SQL server Management Studio, look under "SQL Server Agent" and you will find "Jobs". Here you can create a job to execute with a Daily Frequency of every hour.

Does this help?
Cool | :cool:

Elham M3-Feb-11 3:41

Elham M

3-Feb-11 3:41

Thanks but I'm not logged in as "sa". please explain more about job?
I see it in sql server agent but I didn't work it.I should update table data every hour

David Mujica3-Feb-11 5:51

David Mujica

3-Feb-11 5:51

If you don't have sa access, then I would write a windows command line utility (no GUI) which can be scheduled in the Windows Task Schedule to run every hour to update your table.