Solved

problems with Query INSERT IN

Posted on 2004-09-14
22
238 Views
Last Modified: 2010-05-18
Guys,

I need to take the data from a SQL Server 2000 Database to a Access Database. To accomplish this task, first I tryed to do from a Access database to another access database, so I wrote the code below:

                   Dim Conn As New OleDb.OleDbConnection(StringConn3)

                    Conn.Open()

                    SQL = "INSERT INTO " + Ds.Tables("carregatabela").Rows(j)("TableName") + " ( " + VarCamposInsere + " ) IN '" + ArqDes + "'"
                    SQL += " SELECT " + VarSelect
                    SQL += " FROM " + Ds.Tables("carregatabela").Rows(j)("TableName")

                    Dim Insert As New OleDb.OleDbCommand(SQL, Conn)

                    Try
                        'Executa a Query acima
                        Insert.ExecuteNonQuery()

                    Catch ex As Exception
                        MsgBox(ex.Message)
                        'Exit Sub

                    End Try

                    Conn.Close()

where:
ArqDes: Is the destiny path of the access file
StringConn3: Is the origin OLEDB connection of the access file.

When I do this, everything works fine. But when i change the StringConn3 to a SQL Server Connection, using a SQLOLEDB, the system give me the follow error:

"Error in IN syntax. "

Does anyone knows where is it wrong?

Thanks in advance

Angelo
0
Comment
Question by:cyberwalker2000
  • 7
  • 7
  • 5
  • +2
22 Comments
 
LVL 15

Expert Comment

by:jdlambert1
ID: 12058172
Do a debug print of SQL right before it's executed and post the exact SQL statement that it's trying to execute.
0
 
LVL 10

Expert Comment

by:Jay Toops
ID: 12058185
please dump a sample of the actual SQL generated and paste it here

I belive that the syntax you are using is invalid for sql server ..

Jay
0
 
LVL 10

Expert Comment

by:Jay Toops
ID: 12058192
DANG is there an echo in here or what

Jay
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

Author Comment

by:cyberwalker2000
ID: 12058197
ok. That's is the Query:

"INSERT INTO Entrada ( [CodigoEnt],[Data],[NumNf],[CodForn],[Nome],[VrTotal] ) IN 'D:\Visual Studio Projects\BackBD\bin\Bdtemp\BDNatco_ADM.mdb' SELECT Entrada.[CodigoEnt],Entrada.[Data],Entrada.[NumNf],Entrada.[CodForn],Entrada.[Nome],Entrada.[VrTotal] FROM Entrada"
0
 
LVL 10

Expert Comment

by:Jay Toops
ID: 12058264
The Syntax you are looking for is

SELECT *
FROM [c:\path\DbName.mdb].tablename

so your query would be ..

INSERT INTO Entrada ( [CodigoEnt],[Data],[NumNf],[CodForn],[Nome],[VrTotal] ) SELECT Entrada.[CodigoEnt],Entrada.[Data],Entrada.[NumNf],Entrada.[CodForn],Entrada.[Nome],Entrada.[VrTotal] FROM  [D:\Visual Studio Projects\BackBD\bin\Bdtemp\BDNatco_ADM.mdb].Entrada

JAY
0
 

Author Comment

by:cyberwalker2000
ID: 12058503
Now it's giving me the follow error Jay:

"Invalid object name 'D:\Visual Studio Projects\BackBD\bin\Bdtemp\BDNatco_ADM.mdb.Entrada'."


0
 
LVL 9

Expert Comment

by:apirnia
ID: 12058618
If your ultimate goal is to Transfer data from SQL To Access why don't you use a DTS package.

Open SQL Enterprise Manager
Right click on the table you want to export
Then Select All tasks >> Export Data
Then just follow the steps by choosing your Source which is SQL server
and destination which is your MS Access DB
0
 

Author Comment

by:cyberwalker2000
ID: 12058666
yes, I could do that, but the problem is thar my manager want I write a program with that goal because he use these access files like a backup of his databases. So he would need to run the DTS several times for each database we have.

So the goal of this program is to make easier for him to backup his databases.
I already tryed to show him the backup tools that SQL Server have, but he prefer in that way....
0
 
LVL 10

Expert Comment

by:Jay Toops
ID: 12058700
Are you sure that file exists?
it must be EXACTLY that path.

Jay
0
 
LVL 10

Expert Comment

by:Jay Toops
ID: 12058715
Since i gather this is running on the server you will probably have to use a UNC name

like \\MYWORKSTATION\d$\Visual Studio Projects\BackBD\bin\Bdtemp\BDNatco_ADM.mdb

Jay
0
 

Author Comment

by:cyberwalker2000
ID: 12058779
Not yet Jay... First i'm trying to run this tool in my local machine....

What I don't understand is that the query worked with the Access connection, but not with the SQL Server... Can be the connection am i using?

Provider=SQLOLEDB.1;Password=cpd;Persist Security Info=False;User ID=sa;Initial Catalog=BDNatco_ADM;Data Source=172.19.193.71

0
 
LVL 10

Expert Comment

by:Jay Toops
ID: 12058836
The sql syntax is different,
PLUS the query is actually running on the SERVER with sql ... not thru jet on your local machine

Jay
0
 
LVL 9

Expert Comment

by:apirnia
ID: 12058938
You can also use a DSN connection to do this.
0
 
LVL 17

Expert Comment

by:BillAn1
ID: 12060298
set up the accessDB as a  linked server (only need to do this once)
then you can access it easily :

sp_addlinkedserver @server = 'MyAccessServer', @provider = 'Microsoft.Jet.OLEDB.4.0', @datasrc = 'D:\Visual Studio Projects\BackBD\bin\Bdtemp\BDNatco_ADM.mdb.Entrada' , @srvproduct = 'MSAccess'

then you can access (pardon the pun!) it as a regular server -


SELECT * FROM  MyAccessServer...MyTable
0
 
LVL 10

Expert Comment

by:Jay Toops
ID: 12064078
your data source for this should only be
@datasrc = 'D:\Visual Studio Projects\BackBD\bin\Bdtemp\BDNatco_ADM.mdb'
this though

Jay
0
 
LVL 17

Expert Comment

by:BillAn1
ID: 12064144
sorry, yes the datasource is the database file, not the specific table, my mistake.
0
 

Author Comment

by:cyberwalker2000
ID: 12064813
Sorry guys, but i never did this before..

How can I set up the linked server?

and another question:

When I use this query: SELECT * FROM  MyAccessServer...MyTable
I will be doing a Select in a Access file, won't I ? Actually, I want to do a SELECT in a SQL Server and Insert the data inside the Access file....

0
 
LVL 17

Expert Comment

by:BillAn1
ID: 12064866
In Query Analyser, just run this code to set up your linked server (replace the details for your file name/path etc)

sp_addlinkedserver @server = 'MyAccessServer', @provider = 'Microsoft.Jet.OLEDB.4.0', @datasrc = 'D:\Visual Studio Projects\BackBD\bin\Bdtemp\BDNatco_ADM.mdb' , @srvproduct = 'MSAccess'

Then, if you want to insert data into the access file, do

INSERT INTO MyAccessServer...MyTable
SELECT * FROM LocalTable
0
 

Author Comment

by:cyberwalker2000
ID: 12065157
Ok i'll put this code in my system, but are there any problems to execute this code everytime the user run my program? And How does this linked server affect my server?
0
 

Author Comment

by:cyberwalker2000
ID: 12065232
I searched with the Enterprise Manager into Stored Procedures folders, and didn't find any sp_addlinkedserver.

Shouldn't it be there?

0
 
LVL 17

Expert Comment

by:BillAn1
ID: 12066001
It should be in master database.
You only need to run this code once ever. You don't run it every time you want to link servers.
The only effect is that anybody who has permissions to log onto SQLServer will be able to view the Access DB. It won't have any performance issues. Nor can the AccessDB 'link' back to the SQL Server - it is a one-way link only.
0
 
LVL 17

Accepted Solution

by:
BillAn1 earned 250 total points
ID: 12066030
IN case I was confusing, what I mean is you run it once to link the SQL Server to the Access DB. Once the link is made, you can run queries linking the two together whenever youlike, you never have to run the sp_linkserver again. Ofcourse, if you need to link to a different server you will ahve to do it again.
BTW, you can also link the servers in EM by using the Linked Servers option in Security.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
coldfusion upload spreadsheet into the databse 2 41
Count with a subquery showing details 10 41
Help with SQL Server Polygon 2 32
SQL Improvement  ( Speed) 14 26
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now