sql 2000 problem: Insert data to an external table

[lucia]

I am using vb6 and sql server 2000.
I want to insert rows from server table called table1 to table2 located in C:\program File\sample.mdb

Here is my code:

sql="Insert into table2 in 'C:\program File\sample.mdb' "
sql = sql & " From table1 "
sql = sql & "Where ......"

I got an error msg: Incorrect syntax near 'In'.

can any one help me out?

thank a lot.

[umararif]

try this
db1_path="'C:\program File\sample2.mdb' "
db2_path="'C:\program File\sample1.mdb' "
sql="Insert into table2 in "&db_path " from table1 in "&db2_path" where....

[lucia]

Thank you for your reply.
I tried and got the same error msg.

[exterminator]

Thank you for your reply.
I tried and got the same error msg.Use the VB 6.0 Immediate Window to see how the query is built up i.e. the value of the sql string. And post it here or check if it is correct as you want.

[lucia]

Use the VB 6.0 Immediate Window to see how the query is built up i.e. the value of the sql string. And post it here or check if it is correct as you want.

Thank you, Exterminator

Here is the code copied from Immediate Window:

INSERT INTO No_Charges In 'C:\Program Files\Custmain\Report.mdb'

SELECT CM_SNAP.CUST, CM_SNAP.PARS, CM_SNAP.BTYP, CM_SNAP.NAME , CM_SNAP.ADR1, CM_SNAP.ADR2, CM_SNAP.CITY, CM_SNAP.STCD, CM_SNAP.ZPCD, BLS_SNAP.CUST, BLS_SNAP.CHRG, BLS_SNAP.SEQN, ' ' as
From_Date, ' ' as To_Date

FROM CM_SNAP LEFT JOIN BLS_SNAP ON CM_SNAP.CUST = BLS_SNAP.CUST

WHERE CM_SNAP.CRDT Between '19990101' And '19990104'

I format it a little bitter for better review. I am sure it is not the space problem.

Thanks again.

[exterminator]

INSERT INTO No_Charges In 'C:\Program Files\Custmain\Report.mdb'Specify all the column names in No_Charges exactly matching the order of those in the select query.

[lucia]

Specify all the column names in No_Charges exactly matching the order of those in the select query.

Hi,
I tried and here is the sql:

INSERT INTO No_Charges(Customer_Number,Customer_Pars,Customer_Type,Customer_Name,Customer_Address1,Customer_Address2,Customer_City,Customer_State, Customer_Zip,Billing_Customer,Billing_Charges,Billing_Sequence,From_Date,To_Date) In 'C:\Program Files\Custmain\Report.mdb'

SELECT CM_SNAP.CUST AS Customer_Number, CM_SNAP.PARS AS Customer_Pars, CM_SNAP.BTYP AS Customer_Type, CM_SNAP.NAME AS Customer_Name, CM_SNAP.ADR1 AS Customer_Address1, CM_SNAP.ADR2 AS Customer_Address2, CM_SNAP.CITY AS Customer_City, CM_SNAP.STCD AS Customer_State, CM_SNAP.ZPCD AS Customer_Zip, BLS_SNAP.CUST AS Billing_Customer, BLS_SNAP.CHRG AS Billing_Charges, BLS_SNAP.SEQN AS Billing_Sequence, ' ' as From_Date, ' ' as To_Date

FROM CM_SNAP LEFT JOIN BLS_SNAP ON CM_SNAP.CUST = BLS_SNAP.CUST WHERE CM_SNAP.CRDT Between '19990101' And '19990104'

still got the same error msg.


Thanks again.

[exterminator]

I guess mdb is access db file. SQL server files are MDF and LDF extensions...

Let me go back a little and ask you what is it that you are trying to do? INSERT INTO .. IN ..mdb SELECT FROM... ? It is not making sense to me. Are you doing data migration or what? Can you create a connection (to sqlserver or access whatever it is) to read from the source and make another connection (to access of sqlserver whatever it is) to write into? A 2 step process?

By the way, what exactly is the error message? Just overflow?

[lucia]

Thank you.
You are right. No_Charge is an Access table. i am trying to insert some rows of data from server db to No_charge.

Actually I made it work by opening two data sources (Access and Server). And use "AddNew" method to add data from server to Access. But i don't think it is elegant. So... i want to ask you, experts, to figure that out. :-)

The error msg was : " Incorrect Syntax near keyword 'In' "

Thanks again for your input.

[Shuja Ali]

You actually need to use OPENDATASOURCE function in SQL 2000 to communicate with the external databases. A sample of OPENDATASOURCE function would be Select * From OPENDATASOURCE(
'Microsoft.Jet.OleDB.4.0',
'Data Source=C:\Program Files\Custmain\Report.mdb'
).TABLE2

Now for your Insert Command to work properly, Table2 in your Access MDB should be of the same structure as TABLE1 in the SQL Server. And most importatntly the Access MDB should be present on the server itself. You could also specify field list in your insert command if the structure does not match.

[lucia]

Thank you, Ali

You provided the way to open an external datasource. Actually i have no problem to open two datasources and insert data in 2 steps. (like Exterminator suggested).

If I want to use 'Insert', can I use the code you provided and How?

Thanks again.

[lucia]

I tried this:

dim DBPath as string

DbPath = "OPENDATASOURCE('Microsoft.Jet.OleDB.4.0','Data Source=C:\Program Files\Custmain\Report.mdb').No_Charge "

SQLReport = "INSERT INTO " & DbPath
SQLReport = SQLReport & "SELECT ....
(the rest of query is the same as above)

Now I got an error msg:"Invalid object Name 'No-Charge'.

In my report.mdb, there is only one table 'No_Charge' . It has the same structure as the one in server.

Any idea would be appreciated.

[Shuja Ali]

Did you try just running the Select query to test whether you are able to connect to the database (mdb).

Also does the MDB file exist on the SQL Server itself?

[exterminator]

Note that the query must be executed on the SQL Server's end (a connection with SQL Server needed to execute it and not that too Access). Run a small select query to test if the connectivity works and then if you get success use your actual query. That is:

Have a connection established with SQL Server - then run a select query (which will have nothing to do with the SQL server tables etc) on the access db. See if it works.

[lucia]

Did you try just running the Select query to test whether you are able to connect to the database (mdb).

Also does the MDB file exist on the SQL Server itself?

Yes, I did. Everything works fine if I take out the 'Insert' part. mdb file only exists in C.

Thanks.

[lucia]

Note that the query must be executed on the SQL Server's end (a connection with SQL Server needed to execute it and not that too Access). Run a small select query to test if the connectivity works and then if you get success use your actual query. That is:

Have a connection established with SQL Server - then run a select query (which will have nothing to do with the SQL server tables etc) on the access db. See if it works.

I'm actually using the seperate connection (like you suggested last time) to connect to server and Access. They work fine seperately. I am pretty sure the server connection is fine since entire project use that connection. And Access concetion is fine too since I added new records to the table by using 'AddNew' method.

Thanks.

[lucia]

Note that the query must be executed on the SQL Server's end (a connection with SQL Server needed to execute it and not that too Access). Run a small select query to test if the connectivity works and then if you get success use your actual query. That is:

Have a connection established with SQL Server - then run a select query (which will have nothing to do with the SQL server tables etc) on the access db. See if it works.


The query did execute on the server's end.


Thanks.