I regularly run an import process within SQL Server. Is there any way to execute this import through Access? I'm familiar with using pass through queries. The data that is being imported, is from another SQL server. Thank you in advance!!
I regularly run an import process within SQL Server. Is there any way to execute this import through Access? I'm familiar with using pass through queries. The data that is being imported, is from another SQL server. Thank you in advance!!
you could use Access to pull data from Sql server1
and append to table in Sql server2.
By pulling the data from server1 into access, then appending from access to server2. There's no way to use the SQL import process?
How are you doing this? Executing a SP?I regularly run an import process within SQL Server.
If yes, maybe this will help:
How to Execute SQL Stored Procedures from Microsoft Access
I'm using the SQL Server import wizard. The primary reason is the datasource is located on Server A, and the destination is located on Server B. I'm unable to setup a linked server. If a linked server could be setup, I could do a simple append query.
Can a stored procedure use a second server as the datasource?
I am way over my head right now. I've started using SQL Server this year. but at the basic level.
One thought..... it seems you have read privileges from the source SQL Server dB and read/write privileges to the destination SQL Server dB.
In Access, could you not set up two ODBC connections (ODBC_Source and ODBC_Dest), link the two dB's (the tables) and use an append query to import the data?
ssnafu is correct, set up both as linked tables and run a simple append query from read table to write table. The bigger question is why is this being done in access? TBH, this type of process should be done all within Sql Server framework (Sql Agent or SSIS....if you are not using the data in the access db for anything why is it in there?).
Within the network that I exist, not all users have SSIS, but everyone has Access. When I try to append a linked table that resides within an SQL DB, the append query takes a long time. When I use the SQL import wizard it is very quick. I know that I can execute the wizard from a command prompt, but I don't know how to enter the attributes, server name, query, etc.
If you could create the query in the second SQL Server then you could copy it to Access as a pass-thru query. That would be a question for SQL Server experts!
With the two servers not being linked, how do I run a query on one server, using the data on the second server?
When you say not linked, do you mean to Access or to each other? If the second is set up as a linked server in SQL Server, you can use the 4 part naming:
SELECT Blah
FROM ServerName.DatabaseName.dbo.TableName
Within SQL, I'm not able to link the two servers.