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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy