Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
 
  HOME    TRACKER    Visual Basic




Query DHCP


Does any one know how to query the DHCP server for the IP Addresses it has leased out currently.

Thanks in advance for your help.




View Complete Forum Thread with Replies

See Related Forum Messages: Follow the Links Below to View Complete Thread
DHCP And VB
Would it be possible to create a dhcp type server app using VB, for times when we are gaming and no one thought to bring a server?

DHCP
Hi.
Does anyone know of any code for DHCP(how it works).I'm writing a prog. that uses dhcp,but if I call ipconfig/renew_all it works but I can not get an error if the lease is not renewed.so my thoughts are that I need to write something into my prog. Thanks for any help
p.s:when I do run dhcp and it finishes if I hit control,alt,del. I see winoldapp for every time it ran?thanks again

Fun With DHCP Server
I am just playing around and I am trying to create my own mini DHCP server. I started to read RFC 2131 about DHCP. What I learned about it was that the server listens on port 67 and transmits back to the client on port 68. So I came up with the very simple code:


Code:
Private Sub Form_Load()
dhcpserver.Bind 67
text.text = text.text & "Listening on port 67 UDP " & vbNewLine
text.text = text.text & "Waiting " & vbNewLine
End Sub


Private Sub dhcpserver_DataArrival(ByVal bytesTotal As Long)
Dim data As String
dhcpserver.GetData data
text.text = text.text & vbNewLine & "Data: " & data & vbNewLine
End Sub
I can see the dhcp request (I think) all I can see is:

Code:
Data: 
Data: 
Data: 
Data: 
Data: 
Data: 
why can't I see the actual request? The goal of my project is to create a dhcp server that I can use an access database to take the MAC Address do a select statement and return just a few options including the ip,gateway, and a TFTP file.

I just need to know how I can view the Data section.

--John

DHCP Server API & VB.NET
In the process of administrating a Microsoft DHCP Server
I currently use simple batch scripts via a Data Exchange
Server. The batch scripts perform DHCP commands using
NetShell, but this is not an optimal solution due to facts
like poor handling of memory ressources.

I would instead like to implement a solution that
performs the DHCP commands using an API towards the DHCP
Server.

I know that a solution like this can be achieved using
the native C++ programming language which does not
aquire .NET runtime. However I would like to implement
a solution using VB.NET. Can this be done and if yes,
are there any kind of ressources to this subject? Maybe code examples?

DHCP API Call
Okay, I need some help. I currently have a c++ program that request a DHCP Option Tag from a DHCP server. I am having problems getting the same API call to work in VB6. I am hoping somebody can help me with this.

Thanks for any help, Moat.

I found this declare statement:
-----------------------------

Code:
Declare Function DhcpRequestParams Lib "dhcpcsvc.dll" _
(ByVal flags As Long, Reserved As Any, ByVal AdapterName As String, _
ByRef ClassId As LPDHCPCAPI_CLASSID, ByRef SendParams As DHCPCAPI_PARAMS_ARRAY, _
ByRef RecdParams As OUT, ByVal buffer As String, ByRef pSize As Long, _
ByVal RequestIdStr As String ) _
As Long
Here is an example of it's use in C++:
------------------------------------

Code:
// the DHCP Client Options API arrays for getting the options
DHCPCAPI_PARAMS requests[2] =
{{0, OPTION_SUBNET_MASK, FALSE, NULL, 0}, // subnet mask
{0, OPTION_ROUTER_ADDRESS, FALSE, NULL, 0}}; // gateway address

// set-up the actual arrays
DHCPCAPI_PARAMS_ARRAY sendarray = {0, NULL}; // we aren't sending anything
DHCPCAPI_PARAMS_ARRAY requestarray = {2, requests}; // we are requesting 2

// buffer variables
DWORD dwSize = INITIAL_BUFFER_SIZE; // size of buffer for options
LPBYTE buffer = NULL; // buffer for options
IN_ADDR addr; // address in return code

printf("Getting DHCP Options on Adapter [%S]
", wszAdapter);

// loop until buffer is big enough to get the data and then make request
do
{
if (buffer)
LocalFree(buffer);

buffer = (LPBYTE) LocalAlloc(LPTR, dwSize); // allocate the buffer
if (!buffer)
{
OutputError(GetLastError());
exit(5);
}

// make the request on the adapter
dwResult = DhcpRequestParams(DHCPCAPI_REQUEST_SYNCHRONOUS,
NULL,
wszAdapter,
NULL, sendarray,
requestarray,
buffer, &dwSize,
NULL);
}
while (dwResult == ERROR_MORE_DATA);

Dhcp Server! How??
how would i code a dhcp server in vb also , with a config file holder off your isp , and also to ping the gateway , at the same time used for uncpaping cable modems..

Changing To Dhcp
would like to build a utility that goes into the win95 registry and changes networking to dhcp. can someone point me to a cheat sheet or piece of code on how to do this.

thanks,
gobo

Enabling DHCP In All NIC's
Hey Guys,

I need a help to make a VBS to enable DHCP to all Local Connection Networks , but without know a Local Connection name , for example if the name of connection is BRASIL or GERMANY or Local Connection the VBS needs make a change of the IP Fix to DHCP.

I'm so sorry for my bad english , but I'm just a Brazilian Guy.


In resume i need somethink like this Microsoft script , even though this fuc.... script don't run in WinXP SP2. Windows return a error in second line and first caracter anda i'm not a master in VBS programming .....


strComputer = "."

Set objWMIService = GetObject("winmgmts:" _
    & "{impersonationLevel=impersonate}!\" & strComputer & "
ootcimv2")

Set colNetAdapters = objWMIService.ExecQuery _
    ("Select * from Win32_NetworkAdapterConfiguration where IPEnabled=TRUE")
 
For Each objNetAdapter In colNetAdapters
    errEnable = objNetAdapter.EnableDHCP()
Next




Thanks Folks.

DHCP Configuration
Hi there,

Does anybody know how i can get the DHCP config information out of Registry?
All i need is

IP
Subnet mask
Gateway
Metric

Winsock does not provide the last three and i could not find an API that would give me a DHCP breakdown.
Short of trapping the ipconfig shell command into a file and reading it later i have no clue what to do.

Thanks a bunch,


Tom Kartmazov
tkartmazov@scopeworks.com

DHCP Options
Hi,

I would need to query DHCP Options on the client using VB6 or VBScript - any Idea how this could work ?

Thanks

Jerry

Get List Of DHCP Leases
Is it possible to contact the DHCP server and download a list of address leases? We have a DHCP server and I want to write a LAN IM client. I thought of using the computers NETBIOS name to have it recieve messages by sending UDP packets, but I don't want to flood the network with broadcast data (would technically work because we are all on one subnet)... Does anyone have any ideas on how to do this?

Winsock/Proxies/DHCP
Allenright, i hope there are some Winsock experts here that can help me out. I'm writing an instant messaging system (both client and server) using the Winsock control. It's supposed to handle proxies and DHCP addresses. I'm a little stranded now, because i don't really know what proxies and DHCP are, let alone how to handle them with Winsock. Can anyone help me?

API To Renew DHCP Address
Hi All,
Is there an API to renew your DHCP IP address, or can you only renew the address through cmd or your router?

Thanks

DHCP Enable/Disable
Would anyone know if there is a way to enable/disable DHCP in VB 6.0??? If so could you point me to the right direction. Thanks


Reason why : The reason why I would want to this for my laptop... My home network (LAN) is on DHCP and at work I use Static addressing. So to save mysef some time everyday I am writing a program to change the Enable/Disable DHCP and when DHCP is Disabled to assign my Static IP,Subnet,Gateway,DNS1,DNS2 and ESSID to my laptop. So far everything is going good(i think) on this (sense I am new to VB).

DHCP Delete Lease
Does any one know a method for deleting a DHCP lease, I have done an app, that gets the DHCP scope, of a certain machine, and pings each address in the lease, to find out whether the machine is still connected to the network, but would like to delete any lease that cannot be pinged.

Can't Get DHCP Enabled Info From Win 95
Hi

I need to know how to retrieve if DHCP is enabled from a WIN95 machine. I've found some code but it only works on 98+

(see http://www.mvps.org/vbnet/index.html?code/network/ )

Any ideas folks?

cheers

tom

ADSI, WINS, DHCP Whatever!!!
I am desperately trying to write an app that will resolve from an IP address the logged on username.

the scenario is that I have DHCP which means I can't just store the addresses against the username. We do have WINS resolution, but not too sure if thats gonna help me here and I am on an entirely 2000 server environment so active directories is available to me.

All i want to do is from an IP address track down the username.

HELP!!!!

Enable/Disable DHCP In VB6
I am looking for a way to Enable/Disable DHCP in vb6? Also to grab the SSID of a wireless nic. Is this possiable in VB6???

Any help would be great!

Executing And Running A Sql Statement To Create A Query And Save Query In DataBase
I cannot seem to find the syntax needed however to execute the sql or create a query based on the SQL string that I created that can be saved. Can you please take a look at the block of code below and let me know what you think?


Sub Collect_HealthPlan()

Dim Healthplan_Count As Integer 'counts the number of items in Healthplan listbox
Dim CompanySize_Count As Integer 'counts the number of items in CompanySize listbox
Dim CurrentRow As Integer 'Stores index value of current row
Dim ctrlHPlstbox As Control 'Control variable for list box
Dim ctrlNBchkbox As Control 'Control variable for checkbox
Dim ctrlNBEAchkbox As Control 'Control variable for checkbox
Dim ctrlCompSizelstbox As Control 'Control variable for list box
Dim i As Integer
Dim j As Integer
Dim HealthPlan_Selected As Integer
'Dim ConnectDB As New ADODB.Connection
'Dim rsData As ADODB.Recordset
'Dim ADOCommand As ADODB.Command

'Set ConnectDB = Nothing
'Set ConnectDB = New ADODB.Connection

'the block of code below assigns the basic SQL upon which the form will be used to build a query
SQLString = "SELECT [DBP 04 Pipeline Total].[CountOfCompany Name], [DBP 04 Pipeline Total].[Company Name], [DBP 04 Pipeline Total].[Effective Year], [DBP 04 Pipeline Total].[Health Plan], [DBP 04 Pipeline Total].[CountOfDead No Finalist], [DBP 04 Pipeline Total].[Dead No Finalist], [DBP 04 Pipeline Total].[CountOfDead Finalist1], [DBP 04 Pipeline Total].[Dead Finalist], [DBP 04 Pipeline Total].CountOfDTQ, [DBP 04 Pipeline Total].DTQ, [DBP 04 Pipeline Total].CountOfSold, [DBP 04 Pipeline Total].Sold, [DBP 04 Pipeline Total].[Opportunity Type], [DBP 04 Pipeline Total].[Quoted MBRS], [DBP 04 Pipeline Total].[Quoted Subs], [DBP 04 Pipeline Total].[Company Size]"
SQLString = SQLString & " " & "FROM [DBP 04 Pipeline Total] GROUP BY [DBP 04 Pipeline Total].[CountOfCompany Name], [DBP 04 Pipeline Total].[Company Name], [DBP 04 Pipeline Total].[Effective Year], [DBP 04 Pipeline Total].[Health Plan], [DBP 04 Pipeline Total].[CountOfDead No Finalist], [DBP 04 Pipeline Total].[Dead No Finalist], [DBP 04 Pipeline Total].[CountOfDead Finalist1], [DBP 04 Pipeline Total].[Dead Finalist], [DBP 04 Pipeline Total].CountOfDTQ, [DBP 04 Pipeline Total].DTQ, [DBP 04 Pipeline Total].CountOfSold, [DBP 04 Pipeline Total].Sold, [DBP 04 Pipeline Total].[Opportunity Type], [DBP 04 Pipeline Total].[Quoted MBRS], [DBP 04 Pipeline Total].[Quoted Subs], [DBP 04 Pipeline Total].[Company Size]"

'Set dbsNorthwind = OpenDatabase("Strategic Marketing Dashboard.mdb")

'The following block of code initializes the control variables
Set ctrlHPlstbox = lstHealthPlan
Set ctrlNBchkbox = chkNB
Set ctrlNBEAchkbox = chkNBEA
Set ctrlCompSizelstbox = lstCompanySize

'The following block of code assigns the count values to variables for the list boxes (looping purposes)
Healthplan_Count = ctrlHPlstbox.ListCount
CompanySize_Count = ctrlCompSizelstbox.ListCount

i = 1

'The following block of code loops through the Healthplna list box to gather the names of all of the selected health plans
For CurrentRow = 0 To Healthplan_Count - 1
    If ctrlHPlstbox.Selected(CurrentRow) Then
        HealthPlan(i) = ctrlHPlstbox.ItemData(CurrentRow)
        'MsgBox "current item is " & HealthPlan(i), vbOKOnly, "test"
        HealthPlan_Selected = i
        i = i + 1
    End If
Next CurrentRow

i = 1

'the following block of code takes the values for all of the healthplans selected and begins building the query
'from the basic SQL Statement that was assigned to the SQLString variable. The SQL Statement in Querybuilder
'is updated dynamically based on the selections on the form
For i = 1 To HealthPlan_Selected
    QueryBuilder(i) = SQLString
    QueryBuilder(i) = QueryBuilder(i) & " HAVING ((([DBP 04 Pipeline Total].[Health Plan])=" & HealthPlan(i) & ")"
    
    If ctrlNBchkbox.Value = True Then
        j = 1
        QueryBuilder(i) = QueryBuilder(i) & " AND (([DBP 04 Pipeline Total].[Opportunity Type])=" & "New Business" & ")"
        
        For CurrentRow = 0 To CompanySize_Count - 1
            If ctrlCompSizelstbox.Selected(CurrentRow) Then
                CompanySize(j) = ctrlCompSizelstbox.ItemData(CurrentRow)
                QueryBuilder(i) = QueryBuilder(i) & " AND (([DBP 04 Pipeline Total].[Company Size])=" & CompanySize(i) & "));"
                j = j + 1
                
            End If
            
        Next CurrentRow
        
    End If
    
    If ctrlNBEAchkbox.Value = True Then
        j = 1
        QueryBuilder(i) = QueryBuilder(i) & " AND (([DBP 04 Pipeline Total].[Opportunity Type])=" & "NBEA" & ")"
        For CurrentRow = 0 To CompanySize_Count - 1
            If ctrlCompSizelstbox.Selected(CurrentRow) Then
                CompanySize(j) = ctrlCompSizelstbox.ItemData(CurrentRow)
                QueryBuilder(i) = QueryBuilder(i) & " AND (([DBP 04 Pipeline Total].[Company Size])=" & CompanySize(i) & "));"
                j = j + 1
            End If
            
        Next CurrentRow
        
    
    End If
    
Next i
    
End Sub

Let me know if you have a better way to do this..Thanks for all your help!!!

Regards,
Eric




Edited by - bahamaej on 3/3/2005 4:50:53 AM

Crystal Reports & Dynamic Query (even Tables And Fields Changes In The Query At Run Time)
 
Hi there!

I need to link the crystal report to a query that is generated dynamically. I will be knowing about which table to be linked and which fields to be retrieved at run time only. Fields are refered using an alias in all the query output to get a common name for all. I cant use a dataset or TFX file solution as mentioned earlier as i am not aware of the tables and fiels till runtime. Else i have to hardcode all the possible combinations in different tft file and link .

i tried with a temporarytable but that too doesnt seems to work.

Can anyone guide me of any simpler methode of doing this?

Bipin

Count Rows Returned By Query In The Query Strings
i have some problem guys about grid. the prob is like this. im using DBGrid n when i queried it, it return the values ryt ? but i dono how to count the data from the returned query string processing that i put in DBGrid. pls tell me the solution about this pls ? wil u help me guys ? n thx in advanced 4 ur
 help. GOD BLESS U ALL !!!

How To Add Parameters To A Query Created In Microsoft Query
I define the parameter I want (which is data placed in the excel file itself) but when I refresh the cell the sql loosses the parameter.

SSTab Query & Data Repeater Query
I have set up a SStab to make data from a system easier to organise. The system retrives the data correctly but does not update it. I am using the propertychanged method on the datarepeater.

Private Sub txtclosedate_Change()
PropertyChanged "closedate"
End Sub

Public Property Get closedate() As Date
closedate = txtclosedate
End Property

Public Property Let closedate(ByVal newclosedate As Date)
txtclosedate = newclosedate
End Property

Is the Tab affecting it if so where can I find a guide to sort this out- the KB is not that helpful.

Thanks

Frank

Query With SHAPE Command In Query Analyzer ???? Please Help!
Hi,

I am writing a complicated SQL that deals with SHAPE command.
I am using MSSQL 2000. Ok.

The problem is that Query Analyzer dont recognizes SHAPE sintaxe (gives me a "[Microsoft][ODBC SQL Server Driver] Sintaxe error or access violation" message), then is a pain-in-the-*** to keep testing the query directly in VB.

Can anyone tell me if there is some SQL environment where I can test queries with SHAPE command, or if there is any way to trick Query Analyzer to force it to understand this command?

Thanks!

Executing Query Analyzer Query File
Is it possible to execute a Query Analyzer Query File from within VB6? If so, can you show me how? Can the Query Analyzer Query File be converted into a SP?


Thanks in advance!!

Set Query Parameter For Query That Is Report Source
I am trying to assing a value to a parameter defined in one of my queries in an Access Database. The ultimate purpose is to run a report named "Summary" that has this query as the source. The problem is that I've tried different ways to assing the parameters and they don't seem to be working. How would I go about making this code work?

CODE:

Dim db As DAO.Database
Set db = CurrentDb
Dim parQ As QueryDef
Set parQ = db.QueryDefs("Summary")
Dim strGrp As String

Select Case Selection.Value 'Form value entered
Case 0
strGrp= ""
Case 1
strGrp = "One"

Case 2
strGrp = "Two"
Case 3
strGrp= "Three"
Case 4
strGrp = "Four"
End Select

parQ.Parameters("prmGrp") = strGrp
parQ.Parameters("prmSel") = lstSelect.Value 'Assign value specified in Form

DoCmd.OpenReport "Summary", acViewPreview

'This didn't work as it still prompted me to define the parameters.
'So I tried the following Code:

Dim db As DAO.Database
Set db = CurrentDb
Dim parQ As QueryDef
Set parQ = db.QueryDefs("Summary")
Dim strGrp As String
Dim rstQuery As Recordset

Select Case Selection.Value 'Form value entered
Case 0
strGrp= ""
Case 1
strGrp = "One"

Case 2
strGrp = "Two"
Case 3
strGrp= "Three"
Case 4
strGrp = "Four"
End Select

parQ.Parameters("prmGrp") = strGrp
parQ.Parameters("prmSel") = lstSelect.Value

Set rstQuery = parQ.OpenRecordset()
DoCmd.OpenReport "Summary", acViewPreview

'This doesnt work either. I have to other parameters in the query, two dates read off directly as entered in the form, that are not explicitly defined as parameters. This code gives me an error that it expected more parameters. I assume it is the dates. But the dates do have values in the form that are apparently not being read.

Any suggestions? All is appreciated.

Time Elapsed In SQL Query In VB Vrs SQL Query In Access
Hi guys,

Should it be normal for a SQL query in VB to take longer than in Access. Both queries are out to a SQL server and it is taking 30 seconds for MS Access and almost 4 minutes for VB. Is there anything I am missing or are there different engines in each of the SQL queries?

Thanks a lot,

- George

Help Needed To Create Such An SQL Query, A Looong Query?
I am trying to create one long Sql query based on users selection, but keep getting confused, need some guidance. I have created a sample access database and here is my explanation, hope i make sense!

I have three tables with the following data:
Table one: tblAssessment

UserID companyName SportName CountryName
   1 2        6         
   2 4        8    
   3 3        7
 
Table two: tblDropDown

DropDownID ParentID Name
1 0 CompanyName (as CompanyName has DropDownID=1, all the companynames will have parentId 1
2 1 Nike
3 1 Reebox
4 1 Puma
5 0 SportName (as SportName has DropDownID=5, all the Sportnames will have parentId 5)
6     5 Football
7     5 Baseball
8 5 Rugby

Table three: tblSummary

CompanyName SportName CountryName
Nike     Football England
Reebok     Baseball USA
Puma     Rugby NewZealand


Below is my brief explanation of the tables and fields

tblAssessment have 4 Fields:
UserID (PrimaryKey)
CompanyName(this stores an ID that refers to the name in the dropdown table)
SportName(This stores an ID that refers to the sportsname in the dropdown table)
CountryName

tblDropDown has three fields:
DropDownID (Primary Key)
ParentID
Name

The way tblDropDown is designed is that if the Field "Name" has data "CompanyName" and its DropDownID is "1", then
all the companyNames will have ParentID "1", so fields Nike, Reebok and Puma will have parentID "1"

tblSummary has three fields:
CompanyName
SportName
CountryName
This table has pure text data for all fields.

I need to create an sql statement that takes all the selection of a User and update the CountryName field of tblAssessment.

So for example if UserID "1", has CompanyName 2 (which is Nike, from DropDown table) and SportName 6 (which is Football from DropDown table) then lookup
for them two selection from table tblSummary and get the CountryName and update tblAssessment's CountryName Field. So in this case it will be "England" as the tblSummary
has CountryName "England" where SportName is "Football" and CompanyName is "Nike"

Could someone plz guide me how to create such a query?

I have attached a sample access database that mirrors the same above format.

Thanks,

Access Query Vs VBA Query Question
I have some code (Listing 1 below) that produces the following:

SELECT tblECOMain.JobNo, tblECOMain.ECORefNum, tblECOList.SubAssy, tblECOList.PartNo, tblECOList.PartDescription, tblECOList.ManufacturedBy, tblECOList.RevisionLevel, tblECOList.SubChg, [NewQty]-[OldQty] AS Expr1, tblECOList.ECOBy, tblECOList.ECODate, tblECOList.ECOReturnProc, tblECOList.ECOReturnNo, tblECOList.ECOReturnNoReason
FROM tblECOMain INNER JOIN tblECOList ON tblECOMain.ECORefNum = tblECOList.ECORefNum
WHERE (((tblECOMain.JobNo)=99999) AND ((tblECOList.ManufacturedBy)<>'METRO MACHINE') AND ((tblECOList.SubChg)=False) AND (([NewQty]-[OldQty])<0) AND ((tblECOList.ECOReturnProc)=True));

The query runs great, however when run from the command button in VBA for Access it produces the rs.BOF or rs.EOF condition which goes to a no records to process condition. The purpose of the code is to simple check for records! I select records from a form via the "((tblECOList.ECOReturnProc)=True));" condition. If any are selected, then lets perform on those records later.

The initial form (which allows me to check my values) comes directly from a query, not code. The code basically reproduces that same query, but with the "((tblECOList.ECOReturnProc)=True));" set to true, meaning that these are the specific records I want to deal with. When I check the box, the control source is the field ECOReturnProc, for my check box control chkECOReturnProc.

I also tried referring to the control in code instead, so that the last line read (Me.chkECOReturnProc = True)); but this produced a different error. A runtime error of:

-2147217904(80040e10) No value given for one or more required parameters.

Id prefer to do this on read only, forward only since Im doing nothing to the data in this segment but have changed to open keyset, lock optimistic because Ive had similar problems before. There is no difference.

Id have to say my confusion level is on the rise. Ive done this exact thing elsewhere, in several places with no problems. I believe the code is representatively correct because the debug.print produces valid syntax. Sad to say I spent the whole day on this yesterday, and it really is something that should take 30 minutes.

Any help would be greatly appreciated!


Listing 1:
sql = "SELECT tblECOMain.JobNo, tblECOMain.ECORefNum, tblECOList.SubAssy, tblECOList.PartNo, tblECOList.PartDescription, " & _
"tblECOList.ManufacturedBy, tblECOList.RevisionLevel, tblECOList.SubChg, [NewQty]-[OldQty] AS Expr1, tblECOList.ECOBy, " & _
"tblECOList.ECODate, tblECOList.ECOReturnProc, tblECOList.ECOReturnNo, tblECOList.ECOReturnNoReason " & _
"FROM tblECOMain INNER JOIN tblECOList ON tblECOMain.ECORefNum = tblECOList.ECORefNum " & _
"WHERE (((tblECOMain.JobNo)= " & [Forms]![frmReportsListing]![cboJN] & ") AND" & _
"((tblECOList.ManufacturedBy) <> " & Chr(39) & "METRO MACHINE" & Chr(39) & ") AND " & _
"((tblECOList.SubChg)=False) AND " & _
"(([NewQty]-[OldQty])<0) AND " & _
"((tblECOList.ECOReturnProc)=True));"

Debug.Print "SQLCHK: "; sql
rs.Open sql, CurrentProject.Connection, adOpenKeyset, adLockOptimistic

If rs.BOF Or rs.EOF Then GoTo Skip1

SQL Query From Access Query Vs Table
Hello!

Will a simple SQL query from an access query be much slower that one from a table:

SELECT * FROM qryTest (qryTest = "SELECT Name, Adress from tblTest")

vs

SELECT Name, Adresss FROM tblTest

noccy

Multitable Query In A Single Query....
Hi guys!

Long time since last time I ask for your help but now I have hit a wall... and I need your advice again...

I have 2 tables:
- Table A: Has Date field and Qty field [DateIn,Qty].
- Table B: Has Date field, Week field (1-52), Month field (1-12) [DayIn, WeekIn,MonthIn].

What I need is to retrieve the sum of Qty and show the information Daily, Weekly or Monthly.

For the daily part I have no problem:

Query = "Select DateIn, SUM(Qty) FROM Table A GROUP BY DateIn"

My problem is when I tried to do it Weekly or monthly, I don't know how to relate sum qty and GROUP by WeekIn or MonthIn in a single query.

I know I can do multiple querys using the WHERE clause "WHERE DateIn BETWEEN DayIna AND DayInB" And DayIna = First Day of week or month and DayInb = Last Day of Week or month.

Also I can Include the Week and Month fields in table A, but I don't want to have that extra information in every single row of table A.

Thanks in advance...

regards!

Sub-query, Nested Query Or A JOIN?
This should prolly go to Database forum, but there's virtually no one there at this time.

I need to pull rec's from multiple tables based on a single ID and put it all in one rec set. I can generate that query, but here's my problem...

Once I have that rec set, I need to pull another record from one of the same tables already used based on a value from that initial query and have it all in one rec set. Is that clear as mud?

I have company records coming from say 3 tables. One of the tables is an addressbook. Once I find all the related company info I need the address info for a contact name within the company info. Therefore I am trying to pull the company address and the contact's address from the addressbook table and put all the data in one rst.

Can this be done???

MySQL Query Vs Access Query
Ok so I made the switch finally after some headache but am finding query speeds are so much faster than Access. I am running into a problem with one of my Queries.

Access = #DateValue#
mySQL = 'DateValue'

Is that correct? Doug I believe you posted the wrapper for Access using the number sign, do I need to switch it back now or am I going about this wrong?

Why MSSQL Query Is Different With MYSQL Query?
somebody please help me, i have a trouble with this, i used connection to database with this

Code:


rs_xsr.Open "SELECT DISTINCT tblxsr.Mark,tblxsr.Rev FROM tblxsr where tblxsr.Transmittal='" & cmbtrans.Text & "'" & _
" and tblxsr.mark !='E' order by tblxsr.Mark,tblxsr.Number", dbcon, adOpenDynamic, adLockPessimistic


then error show this
Run Time ERRor '-2147217900 (80040e14)
[Microsoft][ODBC SQL Server Driver][SQL SErver] ORDER BY items must appears in the select list if select distinct is specified

and when i dont use "DISTINCT" that error is not show but my data is not showing, how to fix it
somebody know what the different between MYSQL query with MSSQL Query?
if u have tutorial , please send me email
harry@dbsd.co.id

Query Works In Access Query But Not Through ADO??
Hi there,
my sql string send through ADO from an asp page doesn't return any results but when I put the query directly into Access query it works fine. Got any ideas what I might be doing wrong, I'm at a loss. Below is my the query which searches between dates and the code used to connect to the db(it connects ok just doesn´t return anything). Any help would be great.

Cheers in advance
Williery

SELECT Noticias.Noticias_Fecha, Noticias.Noticias_Titulo, Noticias.Noticias_Imagene FROM Noticias WHERE (((Noticias.Noticias_Fecha) Between #10/01/2001# And #20/01/2001#) AND ((Noticias.Noticias_Titulo) Like '*test*')) AND (((Noticias.Noticias_Typo)=1));



Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "Driver={Microsoft Access Driver (*.mdb)};" & "DBQ=" & Server.MapPath("BaseDeDatosGolfNavarra.mdb")            
            
Set rsNoticias = Server.CreateObject("ADODB.Recordset")
                rsNoticias.open strSql,objConn

How To Print The Query Resukts In The Grid &amp; Excel Row Transfer From VB Grid Query
Good afternoon. I use VB6 and have no Crytals Report. I want to print with formatting (headers plus the query) the query result which is displayed on a grid.

Also should the user want to transfer it to Excel, there is a button when you click it whatever you row you have selected onthe query results in the grid is transferred to Excel. the user may use ctrl to choose individual rows int he query grid or the use shift to select a group of rows to be transferred to Excel.

How do you use the import (to restore back up .mdb files) and export (to backup .mdb files) in real time?

Suggestions are welcomed.

God bless,
Alvin

Query For MS-Access: SQL Differences In Access Query Window Vs. Using ADO In Code
OK, many of us have come across the "Like" wildcard issue. When doing an Access query in Access itself, the "*" is used, whereas doing the query thru ADO in code, the "%" is used.

What other situations are there like this? I have a query that uses IIF, INSTR, and UCASE. When I test it in the Access query window, it works fine, but when I produce the same SQL dynamically in my VB program and try to execute it (using ADO), it chokes. Obviously, "standard" SQL doesn't like these VBA functions in the query. Does anyone know (or know where I find documentation on) the specific differences when doing queries for Access in the Access IDE vs. ADO? Thanks.

Transform Query With Sub Query
Is there any way in acccess to perform a Tranform query , and include in the where clause a Sub query?

I always get a error the the filed from the main query is not a valid field in the sub query.

Run Query Within A Query Using Code
I'm having a bit of a headache with trying to run a delete query using the results of a select query, in VB code. My first query runs OK, but when I try running the second query, I keep getting a message, "Object required."

I've enclosed my code that is causing the problem..... if y'all could take a look at it and tell me the erors of my ways, I would be very happy.

VB Code:
Dim rsMstrPN As New ADODB.Recordset    Dim rsDuplicates As New ADODB.Recordset    Dim strSQL As String    Dim strErrMsg As String    Dim lngRecCount As Long    Dim lngTotalRec As Long    Dim strPartId As String '==========================================================================' Variable Initialization Section'==========================================================================    DeletePNdata = False'==========================================================================' Code Section'==========================================================================     strSQL = ""    strSQL = strSQL & "select DuplicatePartsBad.PNID from DuplicatePartsBad "    strSQL = strSQL & "Order by DuplicatePartsBad.PNID"    rsDuplicates.Open strSQL, conJetMasterDb, adOpenStatic,    adLockReadOnly, adCmdText    If Not (rsDuplicates.EOF = True And rsDuplicates.BOF = True) Then        rsDuplicates.MoveFirst        Do While Not rsDuplicates.EOF             strPartId = rsDuplicates!PNID             strSQL = ""             strSQL = strSQL & "Delete * from MasterPN where MasterPN.PNId    = " & strPartId & "; "             Debug.Print strSQL             rsMasterPN.Open strSQL, conJetMasterDb, adOpenStatic, adLockOptimistic, adCmdText        Loop    End If        rsMstrPN.Close    rsDestination.Close


Thaks to all of you who help out this myopic programmer!!

Query On An Access Query !
I have a table with data as follows:

ProductNo, Customer
1, John
1, Simon
1, Andrew
2, Andrew
2, Dave
3, Simon
4, Pete
4, John
4, Dave

I want to display the data in a query as follows:

Product Number, Customers Bought
1, "John, Simon, Andrew"
2, "Andrew, Dave"
3, "Simon"
4, "Pete, John, Dave"


Any ideas how??????

Access Query -&gt; ADO Query Using DSN
Hello,

I have an Access database which links to tables on an MS SQL Server. It has a query which pulls together information from three different tables. Two of the tables are in one database; the third is in another. The query is supposed to grab the first full set of information it finds for a particular production unit (reel). The query is as follows, and works as expected from Access:

SELECT TOP 1 [dbo_reel].[reel_key], [dbo_Roll_Summary].[cust_key_end_user], [dbo_reel].[dt_turnup], [dbo_reel].[avg_speed_fpm], [dbo_reel].[birth_bwt_act], [dbo_reel].[birth_moisture], [dbo_Quality_Data].[Opacity_(6S20)], [dbo_Quality_Data].[TEAR_md_(2M80)], [dbo_Quality_Data].[TEAR_cd_(2C80)], [dbo_Quality_Data].[TENS_cd_(2C10)], [dbo_Quality_Data].[BRT_t_(6T10)], [dbo_Quality_Data].[SMOOTH_t_(4T40)], [dbo_Quality_Data].[SMOOTH_b_(4B40)], [dbo_Quality_Data].[POROSITY_(5S30)], [dbo_Quality_Data].[TEA_md_(2M60)]

FROM dbo_Quality_Data INNER JOIN (dbo_reel INNER JOIN dbo_Roll_Summary ON [dbo_reel].[reel_key]=[dbo_Roll_Summary].[roll_reel]) ON [dbo_Quality_Data].[sample_key]=[dbo_reel].[reel_key]

WHERE ((([dbo_reel].[reel_key])=[Reel Key]) And (([dbo_Quality_Data].[Opacity_(6S20)]) Is Not Null));

Now, I need to convert this query to a form I can use with ADO. I created a DSN for the SQL Server and pass its name to the ADODB.Connection object. I realized that the table names in Access are not those on SQL Server. Through the SQL Server database list and small test SELECT queries I found the database & table names I need and that ADO (or SQL Server?) does not like square brackets in the statement. My "converted" query is:

SELECT TOP 1 rep_snnb.dbo.reel.reel_key, Technical.dbo.Roll_Summary.cust_key_end_user, rep_snnb.dbo.reel.dt_turnup, rep_snnb.dbo.reel.avg_speed_fpm, rep_snnb.dbo.reel.birth_bwt_act, rep_snnb.dbo.reel.birth_moisture, Technical.dbo.Quality_Data.Opacity_(6S20), Technical.dbo.Quality_Data.TEAR_md_(2M80), Technical.dbo.Quality_Data.TEAR_cd_(2C80), Technical.dbo.Quality_Data.TENS_cd_(2C10), Technical.dbo.Quality_Data.BRT_t_(6T10), Technical.dbo.Quality_Data.SMOOTH_t_(4T40), Technical.dbo.Quality_Data.SMOOTH_b_(4B40), Technical.dbo.Quality_Data.POROSITY_(5S30) , Technical.dbo.Quality_Data.TEA_md_(2M60)

FROM Technical.dbo.Quality_Data INNER JOIN (rep_snnb.dbo.reel INNER JOIN Technical.dbo.Roll_Summary ON rep_snnb.dbo.reel.reel_key=Technical.dbo.Roll_Summary.roll_reel) ON Technical.dbo.Quality_Data.sample_key = rep_snnb.dbo.reel.reel_key

WHERE (((rep_snnb.dbo.reel.reel_key)='" & reelKey & "') And ((Technical.dbo.Quality_Data.Opacity_(6S20)) Is Not Null));

So, [dbo_reel] = rep_snnb.dbo.reel; [dbo_Roll_Summary] = Technical.dbo.Roll_Summary; and [dbo_Quality_Data] = Technical.dbo.Quality_Data.

Unfortunately, the converted query doesn't work. When I run the following code, I get a "Run-time Automation" error at the last line:


   Dim rs As ADODB.recordSet
   Dim conn As ADODB.connection
   Dim sql As String

   Set rs = New ADODB.recordSet
   Set conn = New ADODB.connection
' SPNEWBERG14 = ODBC data source name
   conn.Open "SPNEWBERG14"
   sql = (query)
   rs.Open sql, conn, adOpenDynamic, adLockOptimistic


I've also tried simple JOINing queries such as this one, but still get the error.

SELECT TOP 1 rep_snnb.dbo.reel.reel_key, Technical.dbo.Roll_Summary.cust_key_end_user FROM rep_snnb.reel INNER JOIN Technical.Roll_Summary ON rep_snnb.dbo.reel.reel_key = Technical.dbo.Roll_Summary.roll_reel WHERE rep_snnb.dbo.reel.reel_key = '5H0101';

Only the most simple SELECT queries work:

SELECT rep_snnb.dbo.reel.reel_key FROM rep_snnb.dbo.reel WHERE rep_snnb.dbo.reel.reel_key='5H0101';

Perhaps it has something to do with my JOIN statements, but they were essentially (except for the name conversion and removal of square brackets) created by Access and should work--or am I wrong in assuming that?

Thank you for any light you can shed.

- Ben

Need Help With Query!
Hi Everyone,

I created a Perameter query in Access and it works fine in access butwhen I try to run it in VB I get a "Too Few Perameters 1" error. By the way, I'm using an adodc control and i'm connecting it to the database & the query using the Properties menu. It will work fine if I take out the perameter in my query but as soon as I put in [Enter Amount] and I try to run it in VB I get the error.

Thanks

Run A Query?
I am using Access 2000.

What I need to do is to run a query. What would I use to do that?

Run An Sql Query In Vba,...
Does anyone know how to make a recordset on vba coding ? The data is from worksheet in one column,.. i would like to pull it into a recordset control and make some executable query such as "select distinct ..." or others...

Query
Hi all,
I am having one table with 2 fields(id,subject) & the records are

idSubject
1DS
1SS
1SE
2SS
2DS
3 SS
3SE
4SE
5DS

Now My requirement is to retrieve all those records belongs to
Subject = 'DS' & 'SS'

the o/p of the query is below
ID
1
2

(bcoz both 1 & 2 belongs to subject DS & SS)
can any one help how to write the query for this...

select id from tablename where subject='DS' AND subject='SS'
giving me the blank field...

I hope we have to do intersect the 2 queries belong to subject DS & subject SS

How to write query

Thx

SQL Query In Vb
I'm having difficulities with this sql query

It runs perfectly fine in Access but giving this "From Syntax Error" in Visual Basic
Despite the difference in putting quotes and ampersand, Access and Vb are very simular

I honestly don't know why it's not working
Note : The last 2 lines are on one line

strSumSQL = "SELECT BCaseNumber,Sum([Fiscal Year1]) AS [SumOfFiscal Year1]," _
& "Sum([Fiscal Year2]) AS [SumOfFiscal Year2]," _
& "Sum([Fiscal Year3]) AS [SumOfFiscal Year3]," _
& "Sum([Fiscal Year4]) AS [SumOfFiscal Year4]," _
& "Sum([Fiscal Year5]) AS [SumOfFiscal Year5],[Option Mode] FROM tblOpCostsBenefits" _
& "GROUP BY BCaseNumber,[Option Mode] HAVING BCaseNumber = -1515968224 AND [Option Mode]=0"

Query To See If = 0?
I'm not that good with access. I want to create a query that looks at a table and filters out which ones = 0 in the backordered column. how do I do this?

Query A Query?
if you query a tblMain in access to show all records of first name Jim with a query called qryJim

could you do a query on the qryJim to show records where Jim has the last name Smith like this?

Forms!qryJim.combo1

where combo1 held the value of Smith, and when combo1 held the value of Jones would it return records of Jim Jones.

wish I had access at home to try these dumb ideas.

Query
Hi,

I am still a newbie when it comes to vba, so I probably have a rather simple question:

How can I get the results from a query defined in the design mode form a vba module in access. I just want to iterate through the results to compare it with data I am reading from an excel file. I can find a reference to this type of objects (queries) , only forms!!

Thanxs

Copyright 2005-08 www.BigResource.com, All rights reserved