Re: How to select rows from a mapped protected .MDB and insert into local table

mikepallorad@xxxxxxxx wrote:
its

insert into MainOrderTable field1,field2,field3,field4

select field1,field2,field3,field4 from RemoteOrderTable


No, that's not the actual statement: that's what you think the result of
your concatenation will look like (your tables don't have fields called
field1,field2, etc.).

Debug your application (it IS VB, right?) and debug.write the contents
of strSQL to the Immediate window, or put a label control or textbox on
your form and set its Text property to the value of the variable. Show
us the actual sql statement. You need to see it as well so you can
copy/paste it into the sql view of an Access Query Builder to test it.

<snip>
STRSQL = ""
STRSQL = "insert into [MainOrderTable] [TType],[BDate],[BNo],[Ordno]
SELECT [TType],[BDate]"
STRSQL = STRSQL & ",[BNo],[Ordno] FROM "
STRSQL = STRSQL & "[Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=Z:\RemoteSales.mdb;Jet OLEDB:Database "
STRSQL = STRSQL & "Password=remotepwd].[RemoteOrderTable]"

I don't believe using the OLEDB connection string is legal in the FROM
clause of a sql statement. I believe you have to use your original
syntax:

[MS Access;DATABASE=" & Left(Drive1.Drive, 2) & _
"\MappedDB.mdb;Pwd='" & text1.text & "'].[RemoteOrderTable]"

Again, you would have to try it in the Access Query Builder to be sure.

You may need to take this to an Access newsgroup to get Jet-specific
help.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.


.

Relevant Pages

  • Re: SQL insert into Access database is not working
    ... You cannot debug a syntax error in a sql statement without knowing what the ... the Access Query Builder to create a query that does what you want, ... Please reply to the newsgroup. ...
    (microsoft.public.scripting.vbscript)
  • Re: Cant Add Fields to Form
    ... Look at the Record Source property of the form. ... the SQL statement or the saved query before they will be visible to the ... "Jeff Miller" wrote in message ... Hoping I'm posting to the right newsgroup. ...
    (microsoft.public.access.forms)
  • Re: Retriving data from DB
    ... rsRecordset("Name"), which name would I get from the recordset, the first ... > the fields in your table and generate this sql statement for you (and even ... > post to an Access newsgroup for help. ... >> tblSchedule and tlbScheduleAdd ...
    (microsoft.public.inetserver.asp.db)
  • Re: insert into error
    ... > Microsoft JET Database Engine ... > I've done a response.write with the SQL statement and I get this ... it's silly to be forced to worry about delimiters (let alone ... Please reply to the newsgroup. ...
    (microsoft.public.inetserver.asp.db)
  • Re: how to build SQL?
    ... >> using parentheses to group the criteria in your WHERE clause. ... this query using the Access Query Builder, ... Please reply to the newsgroup. ...
    (microsoft.public.inetserver.asp.db)