Visual Basic :: VBA In Access - Run Query (SELECT FROM ...) And Get Recieved Data

See What Data Is Being Sent/recieved
Hello,

Im not sure what the correct term for it is, but i was wondering if anyone knew how to, if its possible, make a programme sit in between a different programme and see what data is being sent and recieved. I want to do this for msn messenger. im pretty sure its possible because i have an addon called Dimension, which can be, and it lets you see all the data be sent and recieved.,

Sop if anyone has any ideas how is it done, or even waht this "idea" is called so i can do some research of my own, i will be very thankful.

Thanks in advance!!!

Outputting Recieved Data.
Hello. I thought that it would be usefull for me to have visual basic as a language that I could somewhat understand, so I decided to try to learn it. I started making my first application. A simple IRC client that connects to a server, displays a userlist, and sends/recieves messages. I have it setup in two forms. A form where you input all your info (server, nickname, etc...) and another forum that has the chatbox and the userlist. The only thing I need to figure out how to do is output data into the userlist to make it display the users in the channel. I also need to figure out how to output recieved messages into the chatbox. I hope what I am saying makes sense. I would greatly appreciate any help. Thanks.

Checking If All Data Has Been Recieved
My problem here is, I need to check whatever a player is online or offline. If I download the site to a string and search the string for that name, then I can check whatever his online or offline.

The problem is, if the player tries checking before I've recieved all, it would give me false information about current player status. Basically, is there a function in Winsock command to check whatever you're recieving data or not?

Select Query In MS Access
Can any one help me out what is wrong with the below query
in MS Access ....

select muid, ExcCode,TotExcCount from
(select muid,ExcCode,sum(ExcCount) as TotExcCount from TimeU where rowDate between #2003-12-02# and #2003-12-03# and server = 'iextest1' Group By muid,ExcCode) TempTable
orderby muid asc

The query works fine till Group By and fails when I am trying to do
Order by

Thanks...

Select Query Between VB And Access
i would like to select a field from a table.(The field i want to select is called Pmodel)
but it keeps giving me an error of (very few parameters)
where i want the selected number displayed in a test field.
I have two table users and printers..
the user has a foriegn key to the pinters table which is the
printers ID number..its called PID in the Users table and ID in the
printers table.

and the code is as follows:

Set rsModel = dbIT.OpenRecordset("SELECT Pmodel from Users,Printers where rsUsers!Snum = staffNumber AND rsPrinters!ID = rsUsers!PID and rsUsers!ID='?' and rsUsers!Sname='?' AND rsUSers!DeptName='?' AND rsUsers=!Pid='?' AND rsPrinters!Bnumber='?' AND rsPrinters!Cnumber=?")

txtModel.text= rsModel!Pmodel

Would u help me plz to get the needed information from a table...
i really appreciate your help..thank u

SQL Query: Select Different Data Types Into One ALIAS With "SELECT ... AS"
I got a problem on a sqlserver 2000 machine:

three datafields in a table: FirstName LastName Age

data types:
FirstName and LastName: Varchar
Age: Smallint

How can I make the following query correct:

SELECT FirstName + ' ' + LastName + ' (' + Age + ')' AS FullName
FROM table

I get this error : "Syntax error converting the varchar value ')' to a column of data type smallint"

I think it is because of the different data types in the Select part ...

Manipulating Recieved Data In Winsock
Heyy all..

It seems that i cannot 'manipulate' information i recieve from a transfer using a winsock control.

For example:

Private Sub sckSend_DataArrival(ByVal bytesTotal As Long)
Dim TheData As String
sckSend.GetData TheData, vbString
<<<<<Problem part>>>>>
If TheData = "whatever" then
msgbox "okay"
end if

i cant get this last part to work. I tried putting the recieved data (TheData) in a text box and then putting the code in the textbox_change but still it wudnt work. Any suggestions??

Winsock Combines Recieved Data?
Hello... I guess I going to have to ask a Winsock question. (And I was doing so well until now)

In the following code, Winsock will receive the data and interperate it as follows:

Code:
Private Sub Winsock1_DataArrival(Index As Integer, ByVal bytesTotal As Long)
  Dim strdata as String

  Winsock1(Index).GetData strdata
  msgbox = strdata
End Sub

Basically its all that is (msgbox is for the sake of telling the data apart, its not really there), but if it gets 2 pieces of information (1 directly right after another or at the same time) it combines the two strings together:

1st receive = "Hi"
2nd receive = "How are you?"
strdata is actually = "HiHow are you?"

How can I fix this? Thanks in Advance!

Access Combobox Select Query
I am working on an Access database with 2 comboboxes that have the following tables associated with it:

Table : class
Field : index
Field : classID
Field : categoryID

Table : category
Field : index
Field : categoryID

What I am trying to accomplish is this:

cbocategory = category.categoryID

cboclass = class.classID

However, for cboclass, I want to create a query where after you've selected cbocategory, it would then give you classes that are allowed for that particular category. For example:

Code:
SELECT DISTINCT classID FROM class WHERE class.categoryID = category.categoryID

Currently, this statement does not work. Does anyone have a better suggestion?

Select Query - Need To Know Access To Answer
Query syntax:
SELECT Models.Model
FROM Products INNER JOIN Models ON Products.ProductID =
Models.ProductKey
WHERE (((Products.ProductID)=[Forms]![subfrm2BD]![Item]));

This is the rowsource property for a combo box which is on a continuous
subform and should populate according to the Item field of the current
record in the subform. The form's parent is 2BD, the subforms name is
subfrm2BD, and there is only one Item field in its recordsource. The
tables Models and products relate on the ProductID(Unique) and
ProductKey(non-Unique) fields, Models lists all models for a particular
product, and the Item field refers to a particular product. There is
also an Item field in the Products table with the product name. There
is a line in the combo box's Enter event combo.requery to refresh the
contents. When the subform is opened alone, everything works fine, the
combo retrieves the models for the item, but when it is opened within
it's parent, when the Enter event fires, an Access inputbox pops up with
the prompt "Forms![subfrm2BD]![Item]" as if Access does not recognize
the expression.

MS Access Select Query Criteria
Good morning everyone.

I am very new to Access and I am not impressed so far.

I have three variables in drop-down boxes called Lockbox, Date, and Batch.

These three items make up the necessary arguments for a Select Query off of a master table. This works well, but to populate the Batch fields I need to put in an Select Query in the Row Source to populate the Bath drop-down with Batch #s where the last 2 #s of the Lockbox equal the first 2 of the batch in the table.

This is my Select statement: SELECT DISTINCT tbl_GC100TestTable.[Batch Number], Left([Batch Number],2) AS LastTwo FROM tbl_GC100TestTable WHERE (((Left([Batch Number],2))=Right([Lockbox],2)));

This retrieves all the Batch Numbers (no duplicates), but doesn't filter out the Batch numbers that don't correspond to a specific Lockbox (ie. LB: 123456 B: 56001, LB: 234565 B: 65001, etc.).

My thought are, when the form loads, the Batch query populates the Batch drop-down and when the Lockbox, and date change; the batch doesn't update according to those changes.

Any suggestions? Thank you, DAVE

VBA Access Select Query Set To Variable
Good afternoon people.

I am trying to create a select query in VBA and assigning the result of the query to a variable.

I am using Access 2003 so I beleive I need to set up an ADO connection versus a DAO.

User = Environ("USERNAME")

SQL = "SELECT [tbl_PC&A].[Security Level] " & _
"FROM [tbl_PC&A] " & _
"WHERE (([tbl_PC&A].[RACF ID])=UCase(User));"

SQL should equal 1, 2, or 3 based on the user's id captured from the system.

Do anyone know how I can make this work?

Thanks in advance, DAVE

Select Query For Access 2000
Hi All,
I m using access 2000 and having a table in which there are some fields of data type double with rounded off upto 2 decimal points. But actully I want to round them upto 0 decimal point to show the client. But the select query for this doesn't work. this is my query

Select Round(Salary,0) from Employee

where datatype of salary is double.

when i run this query through query builder in access it gives correct result but when I run this query through my vb program it gives me following error:
"Undefined function Round"

this error comes whenever I use eithere built in function or user defined funtion

can anybody help me?

Search Trough Winsock-recieved Data?
Need I to search the incomming data, where is an end of command or I recieve in one DataArrival event all the data sent by SendData method?

[Edited by Jhd.Honza on 09-24-2000 at 04:06 AM]

How Can I Map Oledb Parmeters Into A Access Select Query
PARAMETERS cod Text ( 255 ), nam Text ( 255 ), num Text ( 255 ), addr Text ( 255 );
SELECT [INTERNATIONAL RATES].[COUNTRY CODE] AS CountryCode, [INTERNATIONAL RATES].[COUNTRY NAME], INTERNATIONALSUBSCRIBER.NAME AS InternationalContactName, INTERNATIONALSUBSCRIBER.Address AS InternationalContactAddress, INTERNATIONALSUBSCRIBER.ContactNumber AS InternationalContactNumber
FROM [INTERNATIONAL RATES] INNER JOIN INTERNATIONALSUBSCRIBER ON [INTERNATIONAL RATES].[COUNTRY CODE] = INTERNATIONALSUBSCRIBER.COUNTRYCODE
WHERE ((([INTERNATIONAL RATES].[COUNTRY CODE])=[cod]) AND ((INTERNATIONALSUBSCRIBER.NAME) Like "*" & [nam] & "*") AND ((INTERNATIONALSUBSCRIBER.Address) Like "*" & [addr] & "*") AND ((INTERNATIONALSUBSCRIBER.ContactNumber) Like "*" & [num] & "*"));

this is an access query.when i ran it from access it worked fine.To work fine this query must be given the value of cod parameter rest can be left empty
i.e
cod=some string
nam=can be empty
num=can be empty
addr =can be empty
now i have declared four parameters

.Ada.SelectCommand.Parameters.Add(New OleDb.OleDbParameter("@cod", Data.OleDb.OleDbType.WChar, 13))
.Ada.SelectCommand.Parameters.Add(New OleDb.OleDbParameter("@nam", Data.OleDb.OleDbType.WChar, 30, ParameterDirection.Input))
.Ada.SelectCommand.Parameters.Add(New OleDb.OleDbParameter("@num", Data.OleDb.OleDbType.WChar, 30))
.Ada.SelectCommand.Parameters.Add(New OleDb.OleDbParameter("@addr", Data.OleDb.OleDbType.WChar, 70))

here txt means a text box,cmd combo box ds is a dataset
now values to these parameters are given
.Ada.SelectCommand.Parameters(0).Value = Ds.Tables("Country").Rows(CmbCountryNameCode.SelectedIndex).Item(0).To String
If ChkName.Checked = True Then
.Ada.SelectCommand.Parameters("@nam").Value = TxtName.Text.ToString
MsgBox(.Ada.SelectCommand.Parameters("@nam").Value, MsgBoxStyle.Critical)
End If
If ChkAddress.Checked = True Then
.Ada.SelectCommand.Parameters("@num").Value = TxtNumber.Text
End If
If ChkNumber.Checked = True Then
.Ada.SelectCommand.Parameters("@addr").Value = TxtAddress.Text
End If
now when i execute a fill
.Ada.Fill(.Ds, "InternationalNumbers1")
no records are there in InternationalNumbers1 but when i run query in access based on same values it returns records.i think the other three parameters are not mapping properly can some one help

MS Access Query - Select Row With Lastest Date
Hi,

I have the following two example rows in a table...

acc# expdate amt1 amt2
234 6/8/07 5000 10000
234 1/9/07 4000 12000

I want to select the row for with the lastest expdate (the first row). In this table there are thousands of records and I would like to pull out the lastest entry for each acc#. Any help will be much appreciated.

Thanks,

Derek

SQL Select Works With ADO But Not In Access Query Window
I have an sql select that works fine when I run it with ado from my VB6 program. However, when I try to run the same select statement in the Access DB that is called by the ADO in the VB6 program, I get a circular reference error.

Does that make any sense?

Here is the sql statement:

SELECT Format(SFTimingFrom,'MM/DD/YY') & ' - ' & Format(SFTimingTo,'MM/DD/YY') as SFTimingFrom ,Right(SFTxt,(Len(SFTxt)-LenFileExtName)) as SFTxt,SFImpression ,SFElement, SFDetail FROM SponsorCatAndFileInName WHERE SFTxt <>'' AND CatID = 22 AND NameID = 13 AND SponsorID = 56 ORDER BY SFTxt ASC

Here is the ADO that calls it and works just fine:

Set myRS = New adodb.Recordset
Set myRS.ActiveConnection = adoconn
If (pagesize > 0) Then
myRS.pagesize = pagesize
End If
myRS.CursorLocation = adUseClient
myRS.Open sqlTxt

Here is the error I get when I run it in the Access query window:

"Circular reference caused by alias 'SFTimingFrom' in query definitions select list"

How To Create A Select Query In MS Access Programmatically
Dear Friends,

I wish to ask how can I create a select query (not SQL) across multiple tables in an Access database using commands or code from my VB program and how to delete it afterwards from the database as it just serves as a temporary buffering purpose for export.

Returning Data From Select Query To DataGrid
G'day groovers, I'm looking to return data from Access to a Datagrid using a select statement, but I don't know where to start. Could somebody point me in the right direction, or recommend a tutorial.

Thanks

Cbx

How To Run A Select Query In VBA (Access) And Store Values In A Variable
hello

i want to run a select query in VBA (Access) and want to store values in a variable like

str = "select CountryCode from Country where CountryName = '" & Combo22.Text & "';"

here combo22.text is the name of combo bo from where the value for where clause will b selected . now after getting this value i want to store it in a variable and then want to use it in insert query.

like

DoCmd.RunSQL "Insert into table (val1,val2) values(txtval.Text,str);"

can anybody tell me how to do it in VBA
how to execute a query in access in VBA code

and second question is that how after execution of a query we can use that reults to poulate in a combo .

waiting for an early reply
regards
Naheed

Data Environment / Select Query / Date Func
Hi All,

I am using Data Environment tool with my Access Database. In one of my SELECT Query , I tried to filter the results with respect to current DATE ( short format ).

When I tried to define this select query in Data Environment Design mode, I couldnt figure out which built in function I should use in Criteria section to indicate that I need the results that have today's date.

Does anybody know which function do I need, and the syntax?

Currently , I am filtering dates with code inside my Query Loop, which in turn I believe that it is not the optimized way to do that.

Thank you.

ADODB Query To SQL Server - Select Data From DB To Put In Text File
Hallo, I'm trying to create a connection and retrieve some fields from the database.....it's my first time doing this, and my look-up on the web has given me many codes which dont work when i try to tweak it for my program.

i'm trying to make a conn to the database with the details below: i'd wanna take out the fields and later on place them in a Text file...record by record...(separated by vbTab). My main concern is i wanna go to the first record, take out the data, then move to next record till all data has been read and extracted...Thanks...Any help is great!

'//////////////////////////////////////////
Dim Cn As New ADODB.Connection
Cn.ConnectionTimeout = 100
Cn.Open "Driver={SQL Server};Server=dc100;Uid=sa;Pwd=;Database=mas_db"

Dim rs As New ADODB.Recordset
rs.Open "select * from mas_db"
While (Not rs.EOF)
msgbox rs(0)
Wend
rs.Close
'/////////////////////////////////////////

Cheers,
Adrian

Seek and you shall find...

Use Data From Access Query In VBA
Hello,

I have a query which contains the actual output I want from a database. But I don't want to present the data in a report, nor do I want to export the query results to a txt-file, I want to define how to write the txt file myself.

The question is: how do I access the query from the VBA code? Which variables do I have to define to retrieve for example the number of records of the query in VBA?

How do I get the value of a certain field of a certain record of the query?

Thanks a lot!

Jochen

Query Data From Access
I have a linktable in access linking to a dBASE file which contains the following records out of more then 50,000 records:
FILE NAME = SCF016
RECORD NOWORK SQRMWORK
1 WOL120519 1
2 WOL120519 2
i use query in access to query for this data using a simple sql statement and it can display out the 2 record in the datasheet screen.

But the problem was in visual basic program with the same SQL statement, i can only manage to retrieve 1 record count where the sqrmwork = 1. Below is the coding that i use. Did i make any mistake somewhere or Is there any better way to get the correct data out and not affect the data loading speed?

txtWOS="WOL120519"
strsql = " SELECT * FROM SCF016" _
& " WHERE SCF016.NOWORK='" & txtWOS _
& "' AND SCF016.SQRMWORK>0" _
& " ORDER BY SCF016.SQRMWORK"
Set wos_scf016 = olddb.OpenRecordset(strsql, dbOpenSnapshot)
xcount = wos_scf016.RecordCount

Access Data To Excel Using CrossTab Query??
Hi ... I am trying to import Access data to excel woksheets using a crossTab query. I need to use a search criteria in excel. I have no clue to convert the crosstab query to excel VBA code. Can anyone help me with this ...

The Query looks like this :

TRANSFORM Sum(try2.Charge) AS SumOfCharge
SELECT try2.ServiceID, try2.ServiceDescription
FROM try2
GROUP BY try2.ServiceID, try2.ServiceDescription
PIVOT Format([DateOut],"yyyy");

try2 is another query:

SELECT tblService.ServiceID, tblService.ServiceDescription, tblCar_Service.DateOut, tblService.Charge
FROM (tblCar_Service INNER JOIN tblCar_Service_Details ON tblCar_Service.CarServiceID = tblCar_Service_Details.CarServiceID) INNER JOIN tblService ON tblCar_Service_Details.ServiceID = tblService.ServiceID;

Can anyone tell me how one canuse a crosstab query with excel. or can we make parameterized queries with crosstab.

Cheers, Nit

Paste Access Query Data Into Excel
I work a lot using Access & Excel (v2000).
I use Access to store data (imported from other systems) and Excel for reports.

Is there a way to push data from Access query into specific cell/range/sheet in a workbook?

I tried the TransferSpreadsheet macro in Access. but it seems it doesn't support export into specific range/sheet.

Thanks.

Retrieve Data With Help Of Vb6 From An Access 2003 Query
Hello everybody,

i'd like your help to solve a problem. I have the following query in a vb6 form

sSQL = "SELECT TBL_CANDIDATE.TBL_FNAME, TBL_CANDIDATE.TBL_LNAME, MAN_Result.Cand_Number, MAN_Result.Date_of_Exam, MAN_Result.CountOfResponce_Time, MAN_Result.AvgOfResponce_Time, MAN_Result.MinOfResponce_Time, MAN_Result.MaxOfResponce_Time, MAN_Result.Errors FROM TBL_CANDIDATE INNER JOIN MAN_Result ON TBL_CANDIDATE.TBL_ID = MAN_Result.Cand_Number;"

also i have the following string

oRs.Open sSQL, goCnn, adOpenKeyset, adLockBatchOptimistic, adCmdStoredProc

and when i try to execute the program i take the answer and then the procedure stops:

expected query name after EXECUTE

Thnx in advance

MS Access Data Transfer To VB Related Query
I've used some VB code to query an access database and one of the queried
fields is a bitmapped graphic. My question is, how can I now work with this
graphic? (e.g. put it on clipboard/on a form/etc). If I try to, for
example, put it on the clipboard, it says that this object does not support
this property, am I going about it totally the wrong way? Currently, the
records are held in a recordset, and I'm fairly sure it's queried alright at
I can access all the text-based fields.

Thanks For any help

Michael

What Data Gets Transferred Using An SQL Query And An Access Database
I have written a visual basic program that uses SQL statements to pull data from an Access 97 database.

The database connectivity I use is ADODB if this is relevant.

My question is what data gets passed down the line.

I have found if I have a large table with thousands of records then if I write an SQL statement to read one record (eg a couple of bytes), I find that 200,000 bytes gets transferred.

Subsequent, SQL statements do not pass any data down the line (eg the entire table), but the program can still use the data.

It looks as if although I have only asked for one piece of data, then the whole lot gets downloaded!! Is this how a client server database should work??

(I have a program installed that tells me how much data is being downloaded by my network card.)

My instinct tells me the data or indexes are being downloaded into a client side cache - but this is not what I want. All I want to go up is the SQL statement, and all I want to coem down is the resultant data.

Displaying Data From Query To ACCESS Database
When I execute this code I can not get any of my txt or lbl cntrol to populate from my query. I am running VB6 and Access 2000. I test my connection before I try to execute the code and it tells me my connection is open, However I open the connection in the Module1.bas.

Could some one please tell me what I am doing wrong?

Private Sub cmdGetQuetion_Click()
    Dim sQuest As String


    'Set up the Command to run the query
    moCommand.CommandType = adCmdText
    moCommand.ActiveConnection = moConnection
    moCommand.CommandText = _
        "SELECT QuestionTxt FROM Questions;"
    lblSQL = "SQL: SELECT QuestionTxt FROM Questions;"

    'Run the query
    Set rsMain = moCommand.Execute
    If (rsMain Is Nothing) Then
        MsgBox "Execute of Get Question Failed"
        Return
    End If

    If (rsMain.EOF And rsMain.BOF) Then
        MsgBox "No Question found"
    Else
        lstUsers.Clear
        'Loop Through entire record set
        Do While (Not rsMain.EOF)
            'Assign each field value in turn to a local variable
            sQuest = rsMain!QuestionTxt
            'cBalance = rsMain!balance
            txtQuestion.Text = "sQuest"
            'lstUsers.AddItem sUser & ", balance = " & cBalance
            rsMain.MoveNext
        Loop 'End of do while not EOF loop
    End If 'End if EOF and BOF
End Sub

Use Ms Access Query As A Data Source For Mail Merge
Hi All,

I have written a program in VB 6.0 which uses mail merge feature of MS word. As a data source i am using MS Access query. It works fine with MS word 2000. But when I try to run the app on the machine which has MS word 2003(the reference used for word app is MSWORD10.OLB in VB project) or MS word 2002, it gives me a "Confirm data source" dialog box every time before the merging starts. Is there any way i could avoid it programmatically? Or by changing any settings in MS word?

Awaiting help!!!!!!!!!!!!!!!!!!!!!

Thank you

Reena

SQL QUERY HELP(currency/Number Data Type Ms Access)
hi guys... can you help me regarding a sql query... im using ado and msacess as database

VB Code:
Dim KT_sss As New RecordsetDim lv_sql1 As StringDim lv_whole As Double lv_whole = 13000 lv_sql1 = "select * from sss"lv_sql1 = lv_sql1 + " where sal_credit <= " & lv_whole & "" Set KT_sss = New Recordset KT_sss.Open lv_sql1, gd_local, adOpenStatic, adLockOptimistic

sal_credit field is declared as currency data type in my msaccess database.. my error is too few parameters expected... how can i solve this?...

thanks in advance........

Open Access Query Using System.Data.OleDb
The name of the query is "contacts_query" HOW I DO TO INVOKE IT , AND USING IN A DATASET ?

Using An Access Query As A Stored Procedure In Data Environment
hi,
i have been doing reports (based on an access database) using access because it's the most convenient. I have queries created in access and i use them and pair them with forms to generate my reports. however, my boss wants me to create the reports in vb.

when i tried using the data environment, i saw that in the properties of a command...
source of data: stored procedure
object name: <ONE OF THE ACCESS QUERIES I HAVE TO USE is available here!!!>

so, how exactly do i apply this access query to create a datareport? sorry if this seems a stupid question...

THANKS IN ADVANCE!!!

Query Data From Access By Writing A Code In Excel
Here is what I am doing - create a code in Excel to bring data from Access Database in the same folder. Two problems on the following code: 1) when I used double quote for DatePart , the quoted part (like "yyyy") is highlighted as compile error. 2) it also shows the problem in the line of " .Open, , 3, 3 "

I got this code from a friend and just make minor change and expect it to work propertly. It turned out i met these two problems. Please give me a hint how to fix it. Many thanks.

Code:

Sub Import2()

Dim cn As Object, rs As Object, myCallYear As String, myCallMonth As String
Dim MySql As String, dbfullname As String, myCnt As Long

dbfullname = "C:Documents and SettingsxyzDesktopFFR.mdb"
myCallYear = Sheets("Reference").Range("E13").Value 'Pass Year
myCallMonth = Sheets("Reference").Range("E14").Value 'Pass Month

MySql = "SELECT DatePart(''yyyy'',[TransactionTime]),DatePart('m',[TransactionTime]),[Category],[Subcategory],Sum([DollarSpend]),[TransactionTime]" & _
    "FROM tblExpense GROUP BY DatePart('yyyy',[TransactionTime]),DatePart('m',[TransactionTime]),Category,Subcategory" & _
    "Having DatePart("yyyy",[TransactionTime]) ='" & myCallYear & "'" & "AND DatePart("m",[TransactionTime]) ='" & myCallMonth & "'Order By DatePart("yyyy",[TransactionTime]), DatePart("m",[TransactionTime]);"

myCallYear = Empty
myCallMonth = Empty

Set cn = CreateObject("ADODB.Connection")
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" _
       & dbfullname & ";" 'Create DB connection

Set rs = CreateObject("ADODB.Recordset")
With rs
    Set .ActiveConnection = cn
    .Source = MySql 'Pass your SQL
    .Open , , 3, 3 '.Open , , adOpenStatic, adLockOptimistic
    myCnt = .RecordCount
    If myCnt > 0 Then
        .MoveLast: .MoveFirst
         'Pull data to first sheet, cells a1:RecordestCountRow & column 3 _
            3 fields in the sql pass
        Sheets("Data").Range(Cells(2, 14), Cells(myCnt + 1, 21)).CopyFromRecordset rs
    End If
    .Close
End With
cn.Close
Set rs = Nothing: Set cn = Nothing

End Sub

Need Help In Transfering Data From Access Query To New Excel File.
Hi,
I need help in transfering data from access query to new excel file.

But i need it in code format, i need to write a code so that when i click a button in access form it should transfer query data to excel, it should be
book1 like when i open new excel.

And i am using DAO, but access doent take range methods.

If anyone of you know how to do this, can you please help me.

Veeresh

Print Single Line Of Data From MS Access Report/table/query
Hello,

I currently have a vb project in which I can view reports. What I need is...to be able to have a button on my screen in which I can just printout the last recorded data straight to a printer. I guess I need to know how to query the data through VB and then how to print it straight to a printer.

Sounds simple...but is it?

Thanks!

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.

Passing Data From A Query Table To An Aggregate Table(MS Access 2000)
hello....I am currently developing an accounting system for a specific company here in the Philippines...Can anybody help me or give me a code...I have an aggregate table named AggSales to be used as a source data for my report(Crystal Report 8.5), which is a comparative Profit & Loss report of two months...Now my problem is, i want to pass data from a query table named qryGenLedger to the AggSales table by which the user will select a month and a year of their choice(Month1,Year1 and Month2, Year2)...so that the data in the AggSales will be composed only by Dates which matches the choice of the user(Month1,Year1 and Month2,Year2)...thanx...please help!!!

"Data Type Mismatch" Error Using Command Object/adInteger Parameters/MS Access Query
I'm used an ADODB.Command object to execute an "action query" in an MS Access database and I'm getting some odd errors.

I have setup the action query sort of like a stored procedure - it takes input parameters and executes an INSERT statement.

This is the code in the action query (called InsertInt):

INSERT INTO IntegerTest(FieldID, Value)
VALUES ([@FieldID], [@Value]);

And this is the VB 6 Code:

Dim FieldID As Integer
Dim Value As Integer

FieldID = 12
Value = 35

...

DB Connection info removed

...

cmdObj.CommandType = adCmdStoredProc
cmdObj.CommandText = "InsertInt"

cmdObj.Parameters.Append cmdObj.CreateParameter("@FieldID", adInteger, adParamInput, , FieldID)

cmdObj.Parameters.Append cmdObj.CreateParameter("@Value", adInteger, adParamInput, , Value)

cmdObj.Execute

This is the error that occurs:

[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.

The strange thing is that I only get this error when working with integers. I used the following code to insert strings (in a different table):

cmdObj.Parameters.Append cmdObj.CreateParameter("@Value", adChar, adParamInput, 100, CStr(Value))

And it worked just fine.

Any help would be greatly appreciated.

Emails Not Recieved
Hi!
I've developed a component for sending emails with attachments.My problem is the following:The component works for a very short time,then it stops working.The application containing the component is being put on 'hold' and it stays that way until the application 'dies' , so don't recieve the mails.The component looks like this:

Code:

Option Explicit

Public ErrorStrMail As Variant
Dim oMail As CDO.Message
Dim oConf As CDO.Configuration
Dim FileSize
Dim SumSize
Dim oFlds
Dim oFSO
Dim oFile
Dim sFolder
Dim oFolder
Dim user
Private SMTPSrvr As String
Private SMTPPrt As Integer
Private Sndusing As Integer

Property Let SMTPServer(text As String)
SMTPSrvr = text
End Property

Property Let SMTPPort(valu As Integer)
SMTPPrt = valu
End Property

Property Let SendUsing(valu2 As Integer)
Sndusing = valu2
End Property

Public Function SendMail3(toAddr, whoAddr, docSubj, docMsg, ByVal docType As Integer, Optional cc = "", Optional bcc = "", Optional sFolder = "", Optional user = "")
On Error GoTo MailErr
Set oFlds = oConf.Fields
oFlds(cdoSendUsingMethod) = Sndusing
oFlds(cdoSMTPServer) = SMTPSrvr
oFlds(cdoSMTPServerPort) = SMTPPrt
oFlds(cdoSMTPAuthenticate) = cdoBasic
oFlds.Update
Set oMail = New CDO.Message
oMail.From = whoAddr
oMail.To = toAddr
If (cc <> "") Then
oMail.cc = cc
If (bcc <> "") Then
oMail.bcc = bcc
If oFSO.FolderExists(sFolder) Then
Set oFolder = oFSO.GetFolder(sFolder)
For Each oFile In oFolder.Files
If InStr(oFile.Name, "_" & user) > 0 Then
oMail.AddAttachment oFile.Path
FileSize = Format((FileLen(oFile) / 1024) / 1024)
SumSize = SumSize + FileSize
oFSO.DeleteFile (oFile)
End If
Next
End If
End If
End If
If ((docType Mod 2) = 0) Then
oMail.TextBody = docMsg
Else:
oMail.HTMLBody = docMsg
End If
If (SumSize > 10) Then
ErrorStrMail = "Attachment size too large!"
GoTo MailFlee
End If
oMail.Subject = docSubj
Set oMail.Configuration = oConf
oMail.Send
MailFlee:
Exit Function
MailErr:
ErrorStrMail = Err.Number & ":" & Err.Description
Resume MailFlee
End Function

Private Sub Class_Initialize()
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oConf = New CDO.Configuration
Set oFlds = oConf.Fields
FileSize = 0
SumSize = 0
oFlds(cdoSendUsingMethod) = Sndusing
oFlds(cdoSMTPServer) = SMTPSrvr
oFlds(cdoSMTPServerPort) = SMTPPrt
oFlds(cdoSMTPAuthenticate) = cdoBasic
oFlds.Update
End Sub

Am I doing something wrong?Does this have anything to do with ConnectionTimeout not being set properly?

MSN 7 Message Recieved?
hi,

Im looking for help on using MSN Messenger 7 in VB 6. I would like the program to alert me when any message is recieved in MSN Messenger 7. I know you can do this with Windows Messenger and you can easily reference the API for that and then write some code that i don't know, to alert me in some way when a message has been recieved. For some reason only Windows messenger can be referenced and not MSN Messenger 7 which is the one I use.

So, basically if anyone out there knows how to display a message box or some other alert when a message is recieved in MSN Messenger 7 I would really appreciate your help.

Thanks Alot,
EoDTris

Select Query
i'm now doing a question bank.i have created a table in access97 which contain a set of question.the set of questions for each module had its own module code.
the problem: my first form had 6 different command button each represent a module by clickking at any button it had to load to another form which will only display the questions for that particular module code..
the method im using now is by select case but it cant work..
hope to recieved any suggestion...thanks..

Select Query
suppose i have two tables A and B.

A contains abt 100000 (ie large number) records.
B contains lesser (less than a) number of records.

I want to write a query to get all the records where a.id = b.id.
so there can be two simple ways:

a) Select * from a,b where a.id=b.id
b) select * from b,a where b.id = a.id.

Now my question is which of these is the optimal query. Or is there any third query which is the optimal query.
pls gives reasons for ur answer.

SELECT Query
I am trying to use SELECT query like :

SELECT f(field1), field2 ... FROM table WHERE .....

where f() is a visual basic function that transforms field1 into the required value in every record.

But VB gives error with this query. Is ther any problem here? If so, how can I do this?

Thanks for your help.

Select Query.
I am having trouble selecting the date from my database and displaying the date that is the closest to the present date.

I have a table in a database with a field called Date. This is filled in randomly with dates in no specific order. I want to create a query that will select the most recent date and put that value in a text box on my program

Does anyone know what im going on about? If so please help

Sql Select Query
hi all , i have an access table which has ID`s in .
in another table i have the same ID`s with data entries in, but there may be more than one entry to an id. The entries have already been sorted by the latest date and i need all the entries now by the id .What i need to do is select the is from one table match it up to the other table but display all the data entries on one line .Is this possible? something like

Code:
table a
ID
---
1
2
3

table b
ID | entry
--------------
1 a
2 b
3 c
1 a1
2 b1
3 c1
i would need the result:

Code:
id | entry | entry 2
----------------------------
1 a a1
2 b b1
3 c c1
Or ideally:

Code:
id | entry
----------------------------
1 a , a1
2 b , b1
3 c , c1
thanks all in advance