Suspicious SQL

[DS]

Where does one put the IN part for this SQL statement.
Right after the INSERT or where it is now?
It doesn't look right to me.
Also the ending doesn't look good either.
Thanks
DS

NEWSQL = "INSERT INTO tblChartOfAccounts " & _
"(ChartID,ChartName,ChartNumber) IN '" & C:\Red\Reddy.mdb & "' " & _
"Values(" & Forms!frmBSChartOfAccounts!TxtNewID & ", " & _
"" & Chr(34) & Forms!frmBSChartOfAccounts!TxtName & Chr(34) & ", " & _
"" & Format(Forms!frmBSChartOfAccounts!TxtNumber & ")")

 

[Arvin Meyer [MVP]]

What is REDACT()? Does it return what you expect?

Try it on test data. That's the way to tell for sure.

 

[DS]

OK This seems to work....

If Len(REDACT()) > 0 Then
Me.TxtNewID = Nz(DMax("IDNumber", "Table1"), 0) + 1
DoCmd.SetWarnings False
NewSQL = "INSERT INTO Table1 " & _
"(IDNumber,IDName,IDName2) IN '" & REDACT() & "' " & _
"Values(" & Forms!Form1!TxtNewID & ", " & _
"'" & Forms!Form1!TxtInfo & "', " & _
"'" & Forms!Form1!TxtInfo2 & "');"
DoCmd.RunSQL (NewSQL)
DoCmd.SetWarnings True
Else
End If

Is the Syntax OK?
DS

 

[DS]

What is REDACT()? Does it return what you expect?

Try it on test data. That's the way to tell for sure.

It returns the path to the external database. And yes it does work.
I'm more concerned about the end of the statement. ;
Thanks
DS

 

[AccessVandal via AccessMonster.com]

Hi DS,

Here's from Help.

IN Clause
Identifies tables in any external database to which the Microsoft Jet
database engine can connect, such as a dBASE or Paradox database or an
external Microsoft® Jet database.

Syntax
To identify a destination table:

[SELECT | INSERT] INTO destination IN
{path | ["path" "type"] | ["" [type; DATABASE = path]]}

To identify a source table:

FROM tableexpression IN
{path | ["path" "type"] | ["" [type; DATABASE = path]]}

A SELECT statement containing an IN clause has these parts:

Part Description
destination The name of the external table into which data is inserted.
tableexpression The name of the table or tables from which data is retrieved.
This argument can be a single table name, a saved query, or a compound
resulting from an INNER JOIN, LEFT JOIN, or RIGHT JOIN.
path The full path for the directory or file containing table.
type The name of the database type used to create table if a database is not
a Microsoft Jet database (for example, dBASE III, dBASE IV, Paradox 3.x, or
Paradox 4.x).


Remarks
You can use IN to connect to only one external database at a time.

In some cases, the path argument refers to the directory containing the
database files. For example, when working with dBASE, Microsoft FoxPro®, or
Paradox database tables, the path argument specifies the directory containing
..dbf or .db files. The table file name is derived from the destination or
tableexpression argument.

To specify a non-Microsoft Jet database, append a semicolon ( to the name,
and enclose it in single (' ') or double (" ") quotation marks. For example,
either 'dBASE IV;' or "dBASE IV;" is acceptable.

You can also use the DATABASE reserved word to specify the external database.
For example, the following lines specify the same table:

... FROM Table IN "" [dBASE IV; DATABASE=C:\DBASE\DATA\SALES;];

... FROM Table IN "C:\DBASE\DATA\SALES" "dBASE IV;"



------------------------------------------------------------------------------
--

Notes

For improved performance and ease of use, use a linked table instead of IN.

You can also use the IN reserved word as a comparison operator in an
expression. For more information, see the In operator.

 

[DS]

Wow, thank you that was and is very informative. I'm going to keep this
info for future reference. It explains a lot!
DS

 

[John W. Vinson]

Wow, thank you that was and is very informative. I'm going to keep this
info for future reference. It explains a lot!
DS

Note that he did say it's from the Help. You don't need to keep a second
copy... it's only a click of F1 and a search away.

John W. Vinson [MVP]