Error on Insert into statement

[veer]

hello expert !
i have the probleum in insert into query
and i am sending some code of my programe in which i open a connection and use a method to covert the string in to tablename
whether it is right to open a database like this and create a table name like this
if you require the whole code then informed me
[code=vb]
Do While sDirFolder <> ""
If sDirFolder <> "." And sDirFolder <> ".." Then
Set dbchild = Workspaces(0).O penDatabase(txt ChildMDBPath & "\" & sArrayFolder(ic ount) & "\" & sDirFolder)
sTable = sDirFolder
sarray() = Split(StrRevers e(sTable), "\")
sTable = StrReverse(sarr ay(0))
sTable = Replace(sTable, ".mdb", "")
sTable = "V" & sTable
MsgBox " the value in stable" & sTable
squery = "Insert into" & txtparenttable & "IN'" & txtparentMDBFil e & "' select * from " & sTable & "IN '" & txtChildMDBPath & "\" & sArrayFolder(ic ount) & "\" & sDirFolder & "'"
dbparent.Execut e (squery)
End If
sDirFolder = Dir()
Loop
Next[/code]

[BlackMustard]

hello expert !
i have the probleum in insert into query
and i am sending some code of my programe in which i open a connection and use a method to covert the string in to tablename
whether it is right to open a database like this and create a table name like this
if you require the whole code then informed me
[code=vb]
Do While sDirFolder <> ""
If sDirFolder <> "." And sDirFolder <> ".." Then
Set dbchild = Workspaces(0).O penDatabase(txt ChildMDBPath & "\" & sArrayFolder(ic ount) & "\" & sDirFolder)
sTable = sDirFolder
sarray() = Split(StrRevers e(sTable), "\")
sTable = StrReverse(sarr ay(0))
sTable = Replace(sTable, ".mdb", "")
sTable = "V" & sTable
MsgBox " the value in stable" & sTable
squery = "Insert into" & txtparenttable & "IN'" & txtparentMDBFil e & "' select * from " & sTable & "IN '" & txtChildMDBPath & "\" & sArrayFolder(ic ount) & "\" & sDirFolder & "'"
dbparent.Execut e (squery)
End If
sDirFolder = Dir()
Loop
Next[/code]

try writing out the squery string variable in a messagebox before you execute to see what your variables contain and what the sql statement sums up to. use the line [code=vb]msgbox squery[/code] between your lines 10 and 11.
a recommendation is also to add some protection against sql injection - there's an endless supply of articles on the internet. search for "sql injections" and protection - google is your friend ;)

[debasisdas]

You need to rectify the SQL statment .

What is the use of those two IN operators ?

[veer]

hello expert
i am using the two IN operators because
i want to insert the value in txtparenttable which is present at the path shown in txtparentmdbfil e and second IN operator is also for the same purpose
if you have some idea then send me

thanks

You need to rectify the SQL statment

What is the use of those two IN operators ?

[veer]

hello expert
when i put the msgbox between line 10 and 11 then
it shows the whole path from where i am accessing the stable value
is the insertinto statemet is correct if not then send me some idea so that i can execute this Query

thanks

try writing out the squery string variable in a messagebox before you execute to see what your variables contain and what the sql statement sums up to. use the line [code=vb]msgbox squery[/code] between your lines 10 and 11.
a recommendation is also to add some protection against sql injection - there's an endless supply of articles on the internet. search for "sql injections" and protection - google is your friend ;)

[debasisdas]

From your query i am not sure what exactly you are trying to do .

[veer]

Hello
I want to merge the data from multiple mdb files into one mdbfile, the mdb files are in different folders, I think you can understand all this my coding

this will clear you from my whole code ............

[CODE=vb]Dim dbparent As DAO.Database
Dim dbchild As DAO.Database
Dim sDirFolder As String
Dim sArrayFolder() As String
Dim sTable As String
Dim squery As String
Dim icount As Integer
Dim sarray() As String

Set dbparent = Workspaces(0).O penDatabase(txt parentMDBFile)
sDirFolder = Dir(txtChildMDB Path.Text & "\", vbDirectory)
Do While sDirFolder <> ""
If sDirFolder <> "." And sDirFolder <> ".." Then
ReDim Preserve sArrayFolder(ic ount) As String
sArrayFolder(ic ount) = sDirFolder
icount = icount + 1
lblmerge.Captio n = "Directorie s Found"
lblcount = icount
End If
sDirFolder = Dir()
Loop
For icount = 0 To UBound(sArrayFo lder())
sDirFolder = Dir(txtChildMDB Path & "\" & sArrayFolder(ic ount) & "\")
Do While sDirFolder <> ""
If sDirFolder <> "." And sDirFolder <> ".." Then
Set dbchild = Workspaces(0).O penDatabase(txt ChildMDBPath & "\" & sArrayFolder(ic ount) & "\" & sDirFolder)
sTable = sDirFolder
sarray() = Split(StrRevers e(sTable), "\")
sTable = StrReverse(sarr ay(0))
sTable = Replace(sTable, ".mdb", "")
sTable = "V" & sTable
MsgBox " The value in sTable is " & sTable
squery = "Insert into " & txtparenttable & "IN'" & txtparentMDBFil e & "' select * from " & sTable & "IN '" & txtChildMDBPath & "\" & sArrayFolder(ic ount) & "\" & sDirFolder & "'"
MsgBox squery
dbparent.Execut e (squery)
End If
sDirFolder = Dir()
Loop
Next[/CODE]

From your query i am not sure what exactly you are trying to do .

[BlackMustard]

hello expert
when i put the msgbox between line 10 and 11 then
it shows the whole path from where i am accessing the stable value
is the insertinto statemet is correct if not then send me some idea so that i can execute this Query

thanks

if you could please paste here the exact output you get from the msgbox, it would be a lot easier to see exactly what sqlquery you are trying to execute on line 11, and thus to see exactly wherein the error lies.

[veer]

Hi expert
In Line 11
i want to insert the output from sTable which is at the path
"txtchildmdbpat h & "\" & sArrayfFolder(i count) & "\" & sDirfolder"
IN
txtparenttable which is at the path txtparentmdbfil e
please send me solution

thanks

if you could please paste here the exact output you get from the msgbox, it would be a lot easier to see exactly what sqlquery you are trying to execute on line 11, and thus to see exactly wherein the error lies.

[BlackMustard]

Hi expert
In Line 11
i want to insert the output from sTable which is at the path
"txtchildmdbpat h & "\" & sArrayfFolder(i count) & "\" & sDirfolder"
IN
txtparenttable which is at the path txtparentmdbfil e
please send me solution

thanks

i think you still misunderstood my question - at the moment i'm not interested in exactly what you're trying to do, or where you get your values from. the only thing i want to know is exactly, character by character, what the querystring (stored in the variable squery at line 10) is when you're trying to execute it. not where you get it from, not what you want it to do, but simply what string is stored in it at runtime.

[debasisdas]

It is not possible to do what you are try by using SQL statment .

You need to read from the source table and write to target table.

[veer]

when i put the msgbox between 11 and 10 line
then value in squery string =
insert into database IN 'c:\master.mdb ' select * from V001258_1 IN '
p:\new data \ 001258\001258_1 .mdb'
i think this is the answer of your question

i think you still misunderstood my question - at the moment i'm not interested in exactly what you're trying to do, or where you get your values from. the only thing i want to know is exactly, character by character, what the querystring (stored in the variable squery at line 10) is when you're trying to execute it. not where you get it from, not what you want it to do, but simply what string is stored in it at runtime.

[QVeen72]

Hi Veer,

Your SQL Query String Should Read Something like this :

[code=vb]
Insert Into MyDestTable
SELECT *
FROM [C:\SrcDB.mdb].[SrcTable];
[/code]

and this should be executed on the Connection/Database object of the Destination..

REgards
Veena