473,326 Members | 2,023 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Select query in Access

Dear All,
I am trying to select and display a table in access 2007 using vba..i have tried many codes..am unable to complete..kindly help me...

Requirement:
Select 4 tables and append it and should display it..

Using:
Access 2007


Thanks in advance......
May 17 '10 #1
12 2286
code green
1,726 Expert 1GB
Not sure about Access 2007, but isn't there a limit (3 I think) to the number of tables in a JOIN?
May 17 '10 #2
Thanks for the reply..
But still can u help me in appending 3 tables...can u give a sample code..?
May 17 '10 #3
code green
1,726 Expert 1GB
I am not clear what you are trying to do but the basic query you need is
Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM table1 
  2. (INNER/LEFT) JOIN table2 ON table1.id = table2.id
  3. JOIN table ON table2.id = table3.id
  4. .............................
  5. WHERE...
May 17 '10 #4
Thank u...
I have 3 tables in access(am using access 2007)..now i have to combine all these tables into a single one and should display it...i know SQL query for that..But i am unable to implement it in VBA(selecting and displaying a table)...That is the problem...Got it?..Can u help me now?..
May 17 '10 #5
code green
1,726 Expert 1GB
Is this what you mean Quick Tutorial
May 17 '10 #6
NeoPa
32,556 Expert Mod 16PB
SShalini: Thank u...
I have 3 tables in access(am using access 2007)..now i have to combine all these tables into a single one and should display it...i know SQL query for that..But i am unable to implement it in VBA(selecting and displaying a table)...That is the problem...Got it?..Can u help me now?..
As this is all still very unclear what you actually mean, why don't you post the SQL you already know about for starters, then we can possibly get a handle on what you're asking for.
May 17 '10 #7
Hi..
Here it is..i have 4 tables(tbl1,tbl2,tbl3,tbl4) in excel..now i have imported them into access using vba code written in access-vb..each table has 8 fields..now i have to combine all the 4 tables and store it as a new table..then i have to execute a select query which can open and display the new table(table created by combining all the 4 tables)...

DoCmd.RunSQL command works for Create,Update etc..but what about select query?..how to execute select query?..

Am sorry that i couldn't make u understand what i want...I hope i have made things clear now...
May 18 '10 #8
NeoPa
32,556 Expert Mod 16PB
Clear? No.

I asked for your SQL. If you can manage to post that it might make it clearer.
May 18 '10 #9
TheSmileyCoder
2,322 Expert Mod 2GB
This will define a query and open it:
Expand|Select|Wrap|Line Numbers
  1. Public Sub test()
  2.     Dim myqdf As New QueryDef
  3.     myqdf.SQL = "SELECT * FROM tblTest"
  4.     myqdf.Name = "qryTest"
  5.     CurrentDb.QueryDefs.Append myqdf
  6.     Set myqdf = Nothing
  7.     DoCmd.OpenQuery "qryTest"
  8.  
  9. End Sub
I think that is what your trying to accomplish.
May 18 '10 #10
NeoPa
32,556 Expert Mod 16PB
Just to be clear Smiley, that will also create a QueryDef (saved query) in the database and store it for future use.
May 19 '10 #11
TheSmileyCoder
2,322 Expert Mod 2GB
Yes, I realise that. But I dont really know of any other way to make a Select Query result be displayed on screen. Then again, I haven't really looked much into this subject, as I usually present the data in a form/report if its for the user himself to use.
May 19 '10 #12
NeoPa
32,556 Expert Mod 16PB
That was not intended as a criticism Smiley. It's just always a good idea to explain these side-effects. They are probably obvious to both of us, but some readers, less familiar with VBA, may not allow for it unless told beforehand.

Personally, I still feel the OP should respond directly to any questions so that we can provide an answer that fits as closely as we can.
May 19 '10 #13

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: Craig | last post by:
Acces 2000: I have several 'lookup' type tables with a standard field naming convention of id(pk) and name in a medium sized database. When I create a select query involving 3 or 4 of these tables...
3
by: phonl | last post by:
I am a vb6 ADO developer looking at vb.net 2005 and ADO2.net. I used the vb.net 2005 data wizard to bind some controls to a database. Now I want to run a select query and have the bound controls...
5
by: parwal.sandeep | last post by:
Hello grp! i'm using INNODB tables which are using frequently . if i fire a SELECT query which fetch major part of table it usually take 10-20 seconds to complete. in mean time if any UPDATE...
1
by: Phil | last post by:
Is it possible to swap rows and columns in select query output so that each record's data is displayed in a column? I want to collect data each day and display it in a query with each day's date...
5
by: Frank Jovi | last post by:
I am working on a website with ASP.NET 2.0 I started with a SQL database .mdf. After further discussions with my client, we decided to switch to an Access database. I created a Select query...
6
by: Nano | last post by:
I have created ASP file from MS Access. It has the following Code. But it gives an error at: rs.Open sql, conn, 3, 3 The Error is: Error Type: Microsoft OLE DB Provider for ODBC Drivers...
1
by: steveyell | last post by:
Hi there, I have a database with 90,000 property records and I need to split the database into 5 geographic areas. Those areas are defined by postcode districts, and a number of postcode...
3
by: RAG2007 | last post by:
I'm using the QueryDef and Execute method to update a record in my MySQL backend. Problem: When the Passthrough update query is defined using QueryDef, it becomes a select query, and I cannot use...
8
by: Trevor2007 | last post by:
I am trying to hard code the following select query into a select case statement ie (case1 <statment>, case 2 <statment>) but I getteing Compiler error: expected line number or label, or...
2
by: paulmitchell507 | last post by:
I think I am attempting a simple procedure but I just can't figure out the correct syntax. My asp (classic) page runs a SELECT query to obtain dates and ID's from 2 tables uSQL = "SELECT...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.