Append Query to external table 2

[ribhead]

I'm trying to learn this stuff on my own and a lot of help from thif forum so what is wrong with my SQL statement? I'm getting a syntax error?

Private Sub Command0_Click()
Dim strdb As String, strSQL As String
Dim myname As String, mypass As String
strdb = "P:\Toolroom\ToolRoom.mdb"
strSQL = "INSERT INTO tbltooling ([Username],[Password]IN" & strdb & _
"VALUES('" & myname & "','" & mypass & "');"
DoCmd.RunSQL strSQL
End Sub

Any help would be 1derfull

Thanks the rib



Quit saying hot water heater! Nobody wants to heat hot water.

 

[ByteMyzer]

Try:

Private Sub Command0_Click()
    Dim strdb As String, strSQL As String
    Dim myname As String, mypass As String
    strdb = "P:\Toolroom\ToolRoom.mdb"
    strSQL = "INSERT INTO tbltooling ([Username],[Password]IN ''" _
    & "[;DATABASE=" & strdb & ";] " _
    & "VALUES('" & myname & "','" & mypass & "');"
    DoCmd.RunSQL strSQL
End Sub

 

[ribhead]

BytMyzer,


Thanks for the response but still getting a Syntax Error.

Any other thoughts?

rib


Bartender:Hey aren't you that rope I threw out an hour ago?

Rope:No, I'm afraid knot.

 

[PHV]

Put a closing parenthese before the IN keyword.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ribhead]

PH

Still getting a Syntax Error

rib

Bartender:Hey aren't you that rope I threw out an hour ago?

Rope:No, I'm afraid knot.

 

[ribhead]

Never mind it works thanks ByteMyzer, and PH for helping me out.

rib



Bartender:Hey aren't you that rope I threw out an hour ago?

Rope:No, I'm afraid knot.

 

[PHV]

Still an error with this ?
strSQL = "INSERT INTO tbltooling ([Username],[Password]) IN " & strdb & _
" VALUES ('" & myname & "','" & mypass & "');"
Pay attention on the spaces around strdb.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ribhead]

Well,

This is exactly what I have. I just copied and pasted your code in to my code.

Private Sub Command0_Click()
Dim strdb As String, strSQL As String
Dim myname As String, mypass As String
myname = Me.Text1.Value
mypass = Me.Text3.Value
strdb = "P:\Toolroom\ToolRoom.mdb"
strSQL = "INSERT INTO tbltooling ([UserName],[Password]) IN " & strdb & _
" VALUES ('" & myname & "','" & mypass & "');"
DoCmd.RunSQL strSQL
End Sub

Bartender:Hey aren't you that rope I threw out an hour ago?

Rope:No, I'm afraid knot.

 

[willir]

Moot point now, but you seem to also be missing a space in your original SQL statement...

strSQL = "INSERT INTO tbltooling ([Username],[Password[COLOR=blue yellow]]IN[/color]" & strdb & _
"VALUES('" & myname & "','" & mypass & "');"

...in front of the IN clause.

This may explain why you kept getting errors after implementing recommended changes.

Hint:
Copy your SQL statement in the clip board. Start up the query builder but do not add any tables. Switch to SQL view (from menu, "View" -> "SQL view"), paste your SQL statement, and then add a semicolon, ";" to the end of the statement. Attempt to run the SQL statement, and the query builder will highlight the first problem area it encounters.

Richard

 

[ribhead]

OK guys I was a bit premature. Not sure why but I was under the impression it worked but I'm still getting a SYNTAX ERROR. Either I can't see what's wrong or I don't know what's wrong.

Dim strdb As String, strSQL As String
Dim myname As String, mypass As String
myname = Me.Text1.Value
mypass = Me.Text3.Value
strdb = "P:\Toolroom\ToolRoom.mdb"
strSQL = "INSERT INTO tbltooling([UserName],[Password])IN " & strdb & _
" VALUES('" & myname & "','" & mypass & "');"
DoCmd.RunSQL strSQL


rib


Bartender:Hey aren't you that rope I threw out an hour ago?

Rope:No, I'm afraid knot.

 

[willir]

...And you get the syntax error on line...

 

[ribhead]

I just got some time to put the code in SQL view and I still get a syntax error. That's all it says syntax error nothing is highlighted.

Arghhhh



Rib

Bartender:Hey aren't you that rope I threw out an hour ago?

Rope:No, I'm afraid knot.

 

[willir]

...Ok

I setup a test database, and copied your code -- had to tweak since I do not have a P: drive.

My error on the [COLOR=blue yellow]
DoCmd.RunSQL strSQL[/color]
is...

Run-time error '3134'
Syntax error in INSERT INTO statment

I then used the debugger (CTRL-G) to print the SQL statement...
?strSQL
Result
?strsql
INSERT INTO tbltooling([UserName],[Password])IN f:\db\test.mdb VALUES('BigTest','testing');

I copied this to the Query Builder. When I run the query, I get the error, "Syntax error in INSERT INTO statement"

The cursur then points to the f: drive...
INSERT INTO tbltooling([UserName],[Password])IN f[COLOR=blue yellow]:[/color]\db\test.mdb VALUES('BigTest','testing');

Is this similar to your issue? Note that I followed my "hint" to get to this point.

And this worked...

Dim strdb As String, strSQL As String
Dim myname As String, mypass As String
myname = Me.Text1.Value
mypass = Me.Text3.Value

'strdb = "P:\Toolroom\ToolRoom.mdb"
strSQL = "INSERT INTO tbltooling([UserName],[Password])" _
& "VALUES('" & myname & "','" & mypass & "');"
DoCmd.RunSQL strSQL

Are you trying to grab data from an external database that is open? "P:\Toolroom\ToolRoom.mdb"

I do not think this is possible. If it is, you may have to link to the database - the problem with this is that linking tables is fine - but linking to the forms in another database is another issue.

Another tool is the fso, file system object. (You have to add the reference, "Windows Script Host Object Model" or something similar.)

Richard

 

[RoyVidar]

Include single quotes in the path, for instance either:

[tt]strdb = "'P:\Toolroom\ToolRoom.mdb'"[/tt]

or

[tt]strSQL = "INSERT INTO tbltooling([UserName],[Password])IN '" & strdb & _
"' VALUES('" & myname & "','" & mypass & "');"[/tt]

I don't know how many times I've done some mentioning of this in your threads, ribhead, you very, very seldom bother giving enough information to work with. Please, again, take a look at faq faq181-2886, the whole faq is worth a read, but in particular, read #14. It is not easy with comments like "but still getting a Syntax Error. Any other thoughts?" - yes - the thought is PROVIDE US WITH ENOUGH INFORMATION TO WORK WITH!</end umpteenth rant>?

Roy-Vidar