Solved

problems with Query INSERT IN

Posted on 2004-09-14
22
234 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
 

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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

708 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

13 Experts available now in Live!

Get 1:1 Help Now