July 23, 2014 at 9:24 am
Hello All,
I am trying to insert sql data into access database but having the error.
"Test Connection failed of an error in intitalizing provider. Unrecognised database format 'C:\*.accdb'.
Regards
Shaun
July 23, 2014 at 9:30 am
What if you pull (from Access) instead of push (from SQL Server)? The easiest way, if it's a one-off, might be to create a stored procedure and run it through ADO and dump the results into a table.
July 23, 2014 at 9:34 am
the problem is the access database is already built. And data needs to refreshed daily from sql database. So do you know anyway doing it..
July 23, 2014 at 9:40 am
How do you retrieve the data in SQL Server that you want? And how often do you need to insert it?
July 23, 2014 at 9:47 am
The data needs to be refresed daily basis. here is the solution which i had attacged ..built but not working...
The insert data is the access database..
July 23, 2014 at 9:50 am
The way I used to do it was to pull it from SQL Server using Access, but if you can push it from SQL Server, you can create a job and schedule it.
(Sharp as a marble, sorry!) Use OPENROWSET...
http://www.codeproject.com/Tips/43938/Export-data-from-SQL-Server-to-MS-Access
Then if that stored procedure that does that is called by a job, and scheduled, it will take care of running it daily.
Might need more caffeine in my coffee!
Sorr 'bout that!
July 23, 2014 at 10:53 am
just a thought...set up a linked server...that way you can push/pull Access
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 23, 2014 at 12:30 pm
i am trying it but it is giving me an error. is it possible if i sent the access file to you..
July 23, 2014 at 12:52 pm
try this and see if it helps
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO
EXEC sp_configure 'ad hoc distributed queries', 1
RECONFIGURE
GO
EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
GO
EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
GO
EXEC master.dbo.sp_addlinkedserver
@server = N'<linked server name>', ---- eg N'accesstest'
@srvproduct=N'Access 12.0',
@provider=N'Microsoft.ACE.OLEDB.12.0',
@datasrc=N'<your file path to access db>' --- eg N'C:\docs\database.accdb'
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 23, 2014 at 12:59 pm
the command wont work because i have insert only rows which does not match and rows which matched to be updated by a value...
July 23, 2014 at 1:01 pm
Shaun2012 (7/23/2014)
the command wont work because i have insert only rows which does not match and rows which matched to be updated by a value...
what command wont work?
were you able to create a linked server in SQL to your Access database?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 23, 2014 at 1:19 pm
IM not able to create linked server..
July 23, 2014 at 1:21 pm
what error message?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 23, 2014 at 1:24 pm
can i send u the error message tommorrow.... im just leaving work ...
July 23, 2014 at 1:34 pm
Shaun2012 (7/23/2014)
can i send u the error message tommorrow.... im just leaving work ...
pity...have time to help now...but tomorrow will be fine...hopefully someone else will pick up as well
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply