There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.
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
-
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
There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Join & Write a Comment Already a member? Login.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs).
Being someone who is always looking for alternatives to "work your data", I came across this …
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
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 Month5 days, 2 hours left to enroll
580 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.