error query input must contain at least one table or query

[erin_0502]

hai any body can help me....
when i run coding from module error "error query input must contain at least one table or query " come......

any body see one the problem?????????????

 

[Rabbie]

It would be helpful if you could post the content that is giving the error. My telepathic powers don't work so well now I am getting older.

 

[chergh]

Fortunatley I'm younger so my telepathic powers are at their peak. The problem is that there is a problem with your VBA. Now do as Rabbie asked

 

[Ian Mac]

I concur with the others. Post your code please.

I'm not sure how old either o the others are but:
I'd say you need a table or query on your query.

Joking aside, you don't have many posts.

Please read the following and you will find the riches will be flowing:

How to ask questions the smart way

Be mindful of the following:

Be clear and consise with your subject heading, this gets people reading your posts
Be the same with your question:

Say what the problem or challange you have is
How the problem occured, or what is causing the you trouble with the challange
Post any code which doesn't work and explain where it didn't work, or post the code you have got so far towards an answer
Tell us what you have done to try and fix it
Tell what you expect to happen and give example of code/outputs

When people do help you, tell them they have, or explain to them they haven't and why. (Unlike your other 2 posts)

And finally, never post the same question twice.

Sorry if it's off topic, but you will feel the benefits, I promise you this helpful advise not just throwing mud.

Cheers,

 

[erin_0502]

thank Ian..... this is my coding....

Option Compare Database
Option Explicit


'Public Sub Main()
'ExportDatabaseObjects()
'End Sub

'Public Sub ExportDatabaseObjects()
Public Sub Main()
On Error GoTo Err_ExportDatabaseObjects

Dim db As Database
'Dim db As DAO.Database
Dim td As TableDef
Dim i As Integer
Dim strFilename As String
Dim sExportLocation As String
Dim Expression As String
Dim returnValue() As String
Dim strSQL As String
Dim qdfOutput As DAO.QueryDef

Set db = CurrentDb()

strSQL = "SELECT Id, TypeId, "
strSQL = strSQL & "DesignOption, "
strSQL = strSQL & "PhaseCreated, """

Set qdfOutput = db.CreateQueryDef("QueryTestTest", strSQL)

sExportLocation = "N:\- Guest -\guestbcs\usrPrivate\irni\irni\access\"
For Each td In db.TableDefs 'Tables
If Left(td.Name, 4) <> "MSys" Then
strFilename = sExportLocation & td.Name & ".txt"
Debug.Print strFilename
DoCmd.TransferText acExportDelim, , "QueryTestTest", strFilename, False
End If

Next td

For i = 0 To db.QueryDefs.Count - 1
Application.SaveAsText acQuery, db.QueryDefs(i).Name, sExportLocation & db.QueryDefs(i).Name & ".txt"
Next i

Set qdfOutput = Nothing
Set db = Nothing

MsgBox "All table been exported as a text file to " & sExportLocation, vbInformation
Exit_ExportDatabaseObjects:
Exit Sub

Err_ExportDatabaseObjects:
MsgBox Err.Number & " - " & Err.Description
Resume Exit_ExportDatabaseObjects
End Sub

but now i get another error that Object "QueryTestTest" already exist.....

 

[WayneRyan]

erin,

Your strSQL equates to --> SELECT Id, TypeId, DesignOption, PhaseCreated, "

Obviously, you are missing the From clause of the query ... as the message said.

Also, if the query already exists and you just want to change it:

Set qdfOutput = db.OpenQueryDef("QueryTestTest")
qdfOutput.SQL = strSQL
qdf.Close

btw, it looks like all of your tables have the same columns (yes)?

How many tables do you have?

Wayne

 

[erin_0502]

i have 144 table with different column.... this coding only selected for example....coz i try to selected more table, many error come out.... so i do for one table only.....

b4 this i try to this table and fiel

Table Floors
Id
TypeId


Table Levels
DesignOption


Table Railings
PhaseCreated

 

[Ian Mac]

I see what you are doing,

As I have already mentioned, please please please keep to 1 thread, I gave you most of the code you have in another thread. Of which you haven't replied, also mentioned.
For others info:

http://www.access-programmers.co.uk/forums/showthread.php?t=134094

You also haven't actually used the bit I made a comment about:

This creates a temporary query to export, then deletes it.

Which is the line:

DoCmd.DeleteObject acQuery, qryTemp

What are you actully trying to do, in the other thread I asked why you want to export MSys tables to text files??

This does not make sense to me.

Your code won't work this way either, each of the Tables MSys has different field headings in, how do you expect to overcome that, the query:

strSQL = "SELECT Id, TypeId, "
strSQL = strSQL & "DesignOption, "
strSQL = strSQL & "PhaseCreated, """

Is very specific to 1 set of fields in 1 table.
To answer why you are getting the error, you haven't told the query which table to get the info from, '...FROM TableName' as mentioned by Wayne.

Finally, I've done enough hard work bringing your 3 thread together now you need to help us.

Do as I have asked:
Explain what you have
Explain what you are trying to achieve, and more importantly, why

So:

I have a database with 144 table in
What I want is where the table has MSys in the name (I don't why you would) I need for the Jack of Diamonds to jump out of the screen an squirt cider in my ear, etc etc etc.

Help us help you.