Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 245
  • Last Modified:

problems with Query INSERT IN

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
cyberwalker2000
Asked:
cyberwalker2000
  • 7
  • 7
  • 5
  • +2
1 Solution
 
jdlambert1Commented:
Do a debug print of SQL right before it's executed and post the exact SQL statement that it's trying to execute.
0
 
Jay ToopsCommented:
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
 
Jay ToopsCommented:
DANG is there an echo in here or what

Jay
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
cyberwalker2000Author Commented:
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
 
Jay ToopsCommented:
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
 
cyberwalker2000Author Commented:
Now it's giving me the follow error Jay:

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


0
 
apirniaCommented:
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
 
cyberwalker2000Author Commented:
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
 
Jay ToopsCommented:
Are you sure that file exists?
it must be EXACTLY that path.

Jay
0
 
Jay ToopsCommented:
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
 
cyberwalker2000Author Commented:
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
 
Jay ToopsCommented:
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
 
apirniaCommented:
You can also use a DSN connection to do this.
0
 
BillAn1Commented:
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
 
Jay ToopsCommented:
your data source for this should only be
@datasrc = 'D:\Visual Studio Projects\BackBD\bin\Bdtemp\BDNatco_ADM.mdb'
this though

Jay
0
 
BillAn1Commented:
sorry, yes the datasource is the database file, not the specific table, my mistake.
0
 
cyberwalker2000Author Commented:
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
 
BillAn1Commented:
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
 
cyberwalker2000Author Commented:
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
 
cyberwalker2000Author Commented:
I searched with the Enterprise Manager into Stored Procedures folders, and didn't find any sp_addlinkedserver.

Shouldn't it be there?

0
 
BillAn1Commented:
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
 
BillAn1Commented:
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 7
  • 7
  • 5
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now