Visual Basic

ttocsmi25-Sep-12 14:13

25-Sep-12 14:13

Thanks. I tried something similar this afternoon, but it didn't work. I'll try your idea tomorrow.

Bernhard Hiller25-Sep-12 23:13

Bernhard Hiller

25-Sep-12 23:13

Just happens to work with the example data given. It will show wrong results with duplicated entries, e.g. having two times 357,"Start Switch".

Martijn Kok25-Sep-12 23:27

Martijn Kok

25-Sep-12 23:27

You are right. If for some reason you can't prevent duplicate entries, then you can use the following query:

SQL

SELECT ArtworkID
FROM (SELECT DISTINCT ArtworkID, DescriptionText FROM Artwork) as t1
WHERE DescriptionText In ("Start Switch","Stop Switch")
GROUP BY ArtworkID
HAVING COUNT(*)=2;

It uses a sub-query to get the distinct values first. The rest functions just the same.

ttocsmi1-Oct-12 1:56

1-Oct-12 1:56

Nope. Still nothing....

ttocsmi1-Oct-12 1:51

1-Oct-12 1:51

For reasons unknown, this didn't work. My intention was for the search text entries to more resemble wildcard text. For example, return all IDs with "switch" or "cable" or whatever in the DescriptionText field.

Thanks for your response.

Bernhard Hiller25-Sep-12 23:15

Bernhard Hiller

25-Sep-12 23:15

You must join the table with itself. Something like:

SQL

SELECT a.ArtworkID
FROM Artwork a 
INNER JOIN Artwork b
ON a.ArtworkID=b.ArtworkID
WHERE a.DesciptionText="Start Switch" AND b.DescriptionText="Stop Switch"

ttocsmi1-Oct-12 2:12

1-Oct-12 2:12

Well, that worked. I used 'like' for '=' and surrounded the search text with '%' characters.

Now, suppose I wanted to search using up to 5 terms - would I have to nest the inner joins?

This seems really complicated for something I can see with my eyes & describe easily using English words.

(Also, I tried using the Filter By Form command, but it wouldn't work with my form.

Thanks for the feedback.

ttocsmi1-Oct-12 4:34

1-Oct-12 4:34

Surely there's an easier way, but this is how I got it to work (I've updated the table and field names):

SQL

SELECT *
FROM art_callout_table
WHERE artboardnumber IN (
    SELECT artboardnumber FROM art_callout_table 
    WHERE artboardnumber IN (
        SELECT artboardnumber FROM art_callout_table 
        WHERE lrutext LIKE "%start%") 
    AND lrutext LIKE "%light%") 
AND lrutext LIKE "%switch%";

What confused me repeatedly during development, and what confused the users (they were expecting the search to work one way & I was assuming it to work another), is that there are two distinct search methodologies:

1) Using the built-in Access table filtering commands (or a basic SELECT FROM WHERE statement) to narrow down a table of description text values. Repeated filters can be applied to narrow the list further, but if you're looking for "switch" and "start", all you'll get is individual description text records having both values, eg "big red starting switch" or "switch start light" but not "green switch". Not terribly helpful.

2) Use a different search technique to return all ArtworkID values which have "switch" or "start" or "light" strings within their DescriptionText fields, eg don't look just for DescriptionText records similar to "switch start light blinker".

I thought this was going to be much easier than it turned out to be. Thanks all for your help.

Remote SQL server connection in visual basic /VBA

Martijn Kok1-Oct-12 5:13

Martijn Kok

1-Oct-12 5:13

Good you solved it! Thumbs Up | :thumbsup:

bsjhala25-Sep-12 2:17

bsjhala

25-Sep-12 2:17

In a LAN i am having a database on SQL Server express edition.I want to fetch data from this server on some other machine in the same LAN.
I have already done setting in server for allowing remote connections and allowing the firewall setting. etc.

Can anyone tell me from scratch what I have to do on the client PC to fetch data on it in VBA.

Re: Remote SQL server connection in visual basic /VBA

ChandraRam26-Sep-12 3:04

ChandraRam

26-Sep-12 3:04

You can do that using ADO. Create an ADO connection to the remote server and use the ADO recordset objects to retrieve data.

Make sure you have included a reference to ADO (ActiveX Data Objects) in your VBA project.

HTH

wizy@202024-Sep-12 23:05

wizy@2020

24-Sep-12 23:05

in the project i want to build, i already have a school results system and students should be able to request their results via sms.thus by sending their stud. id to the system number(gsm modem number) and the system automatically reading the message, extracting the stud. id and year from the message search the results table for the results and send it back to the student who requested for the results(senders number) i will also store requests to the database

Re: in the project i want to build, i already have a school results system and students should be able to request their results via sms.thus by sending their stud. id to the system number(gsm modem number) and the system automatically reading the mes

Eddy Vluggen25-Sep-12 0:17

Eddy Vluggen

25-Sep-12 0:17

..cool - and what is your question?

If you're stuck with a particular task, then we can help. As is, you only mentioned that you're about to do a project. If you're looking for volunteers, you'd need to open-source it first. Or try the collaboration-forum Smile | :)

Bastard Programmer from Hell Suspicious | :suss:

if you can't read my code, try converting it here[^]

David Mujica25-Sep-12 4:17

David Mujica

25-Sep-12 4:17

You might want to store the cell phone numbers of your students in the db and the incoming message would just be RESULTS. They should not send their student ID. You want to prevents Student A from requesting results for Student B.

Dave Kreskowiak25-Sep-12 4:32

Dave Kreskowiak

25-Sep-12 4:32

Come on! You know security is the ultimate afterthought. Wink | ;)

A guide to posting questions on CodeProject[^]

Dave Kreskowiak

Eddy Vluggen25-Sep-12 4:41

Eddy Vluggen

25-Sep-12 4:41

David Mujica wrote:
You might want to

I'm not gonna build it Roll eyes | :rolleyes:

But good point Smile | :)

Bastard Programmer from Hell Suspicious | :suss:

if you can't read my code, try converting it here[^]

Otto Grunf25-Sep-12 9:02

Otto Grunf

25-Sep-12 9:02

Eddy Vluggen wrote:
what is your question?

Can't you tell from the subject line?

Eddy Vluggen25-Sep-12 9:05

Eddy Vluggen

25-Sep-12 9:05

TL;DR

Bastard Programmer from Hell Suspicious | :suss:

if you can't read my code, try converting it here[^]

Internal Persistent Database

Dominick Marciano24-Sep-12 18:06

24-Sep-12 18:06

I'm writing a suite of software of which one of the programs is client that connects to a server; the client performs actions of behalf of request from the server with both the client and server being windows services.

For the server I need it to store data such as user names, password (actually hashes of passwords), permission settings, etc. I don't want the information stored on our SQL server because other employees have access to it and there is a risk of information being changed, deleted, and it won't be secure for this purpose. I also don't want the data stored on the server is a file (such as a .DAT file) because again other employees have access to the server and there are the same concerns as with the SQL server.

I was trying to find a way to save information internally within the program. I've worked with embedded resources, but only to the extent of extracting an embedded Word template and saving it to folder. I'm assuming there would be a way I could extract some sort of compressed XML file, change the data I need to, and then re-embedding it using a MemoryStream so the file is never written to the HDD. Would this be a way to go? Any body know any good articles explaining the process of extracting and embedding file, changing it, and then re-embedding it?

The company is a small company and over the course of a day I'm estimating that the server service would have to do the extract/re-embedding maybe 20 times so I don't see it being very heavy on resources. However I'm open to any other ideas. My only requirements is no SQL server or local file, secure, and the data is persisted across system restarts.

Thanks in advance for any help, ideas, or suggestions anybody can provide.

Richard MacCutchan24-Sep-12 22:09

Richard MacCutchan

24-Sep-12 22:09

DisIsHoody wrote:
My only requirements is no SQL server or local file, secure, and the data is persisted across system restarts.

How do you expect the data to be persisted if it is not written to disk somewhere? The obvious answer to this is to use an encrypted file which the server holds open with exclusive use. On startup you read the file and decrypt it into memory. Then, every time some of the data changes you re-encrypt it and write it back to the disk file. The actual format of the file is not critical but it should be fairly simple to devise a format that makes it difficult for anyone to figure out the content even if they could crack the encryption.

One of these days I'm going to think of a really clever signature.

Dominick Marciano25-Sep-12 5:24

25-Sep-12 5:24

What I meant about not writting to the HDD was that it wouldn't be written to a separate file. I was hoping to file a way to write the necessary data to an embedded file. Then the data would persist it sul is technically stored on the HDD but within the assembly instead of a separate file.

Richard MacCutchan25-Sep-12 5:59

Richard MacCutchan

25-Sep-12 5:59

DisIsHoody wrote:
I was hoping to file a way to write the necessary data to an embedded file.

But embedded where? Any file needs to exist on the disk so you have no choice but to write it there. If you are thinking of writing into the actual executable so the data is embedded there, then I do not think that is advisable unless you have a thorough understanding of the problems it may cause.

One of these days I'm going to think of a really clever signature.

Dominick Marciano25-Sep-12 6:31

25-Sep-12 6:31

Yes...i was talking about embedding it in the actual program. The program is a window service so there won't be a program folder (at least it is my understanding that window services don't have an installation folder) where I can save the file. Plus since there other employees which have access to the server I'm concerned of someone either intentionally, or unintentionally, deleting the file, or affecting it other ways because they aren't aware what it is for. I've worked with embedding files within executables but I've only extracted them and saved them to the HDD. Do you know of any good articles that discuss the problems of changing files that are embedded within executables so I can weigh my options and decided best way to proceed?

Richard MacCutchan25-Sep-12 6:58

Richard MacCutchan

25-Sep-12 6:58

DisIsHoody wrote:
i was talking about embedding it in the actual program.

Definitely a bad idea. There are lots of ways to protect the file by hiding it and using access control etc. If your organisation allows employees to go around randomly deleting files from your server then you have a management problem that needs to be addressed.

One of these days I'm going to think of a really clever signature.

Dominick Marciano25-Sep-12 7:10