At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!
COURSE of the MONTH
9 days, 11 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
problems with Query INSERT IN
Posted on 2004-09-14
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(Stri
Conn.Open()
SQL = "INSERT INTO " + Ds.Tables("carregatabela")
SQL += " SELECT " + VarSelect
SQL += " FROM " + Ds.Tables("carregatabela")
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
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(Stri
Conn.Open()
SQL = "INSERT INTO " + Ds.Tables("carregatabela")
SQL += " SELECT " + VarSelect
SQL += " FROM " + Ds.Tables("carregatabela")
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
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
7
-
7
-
5
- +2
22 Comments
Do a debug print of SQL right before it's executed and post the exact SQL statement that it's trying to execute.
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
I belive that the syntax you are using is invalid for sql server ..
Jay
ok. That's is the Query:
"INSERT INTO Entrada ( [CodigoEnt],[Data],[NumNf]
"INSERT INTO Entrada ( [CodigoEnt],[Data],[NumNf]
The Syntax you are looking for is
SELECT *
FROM [c:\path\DbName.mdb].table
so your query would be ..
INSERT INTO Entrada ( [CodigoEnt],[Data],[NumNf]
JAY
SELECT *
FROM [c:\path\DbName.mdb].table
so your query would be ..
INSERT INTO Entrada ( [CodigoEnt],[Data],[NumNf]
JAY
Now it's giving me the follow error Jay:
"Invalid object name 'D:\Visual Studio Projects\BackBD\bin\Bdtemp
"Invalid object name 'D:\Visual Studio Projects\BackBD\bin\Bdtemp
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
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
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....
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....
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
Jay
like \\MYWORKSTATION\d$\Visual Studio Projects\BackBD\bin\Bdtemp
Jay
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;Passwo
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;Passwo
The sql syntax is different,
PLUS the query is actually running on the SERVER with sql ... not thru jet on your local machine
Jay
PLUS the query is actually running on the SERVER with sql ... not thru jet on your local machine
Jay
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',
then you can access (pardon the pun!) it as a regular server -
SELECT * FROM MyAccessServer...MyTable
then you can access it easily :
sp_addlinkedserver @server = 'MyAccessServer', @provider = 'Microsoft.Jet.OLEDB.4.0',
then you can access (pardon the pun!) it as a regular server -
SELECT * FROM MyAccessServer...MyTable
your data source for this should only be
@datasrc = 'D:\Visual Studio Projects\BackBD\bin\Bdtemp
this though
Jay
@datasrc = 'D:\Visual Studio Projects\BackBD\bin\Bdtemp
this though
Jay
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....
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....
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',
Then, if you want to insert data into the access file, do
INSERT INTO MyAccessServer...MyTable
SELECT * FROM LocalTable
sp_addlinkedserver @server = 'MyAccessServer', @provider = 'Microsoft.Jet.OLEDB.4.0',
Then, if you want to insert data into the access file, do
INSERT INTO MyAccessServer...MyTable
SELECT * FROM LocalTable
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?
I searched with the Enterprise Manager into Stored Procedures folders, and didn't find any sp_addlinkedserver.
Shouldn't it be there?
Shouldn't it be there?
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.
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.
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.
BTW, you can also link the servers in EM by using the Linked Servers option in Security.
Featured Post
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.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses
Course of the Month9 days, 11 hours left to enroll
762 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.