jet oledb from tsql

I wish to create a new access database each week whos name includes the
curent date, then to create 7 tables in the database eg '27 Jul 2006.mdb' on
e
for each day
I then wish to insert rows into these tables,
currently I use managed adox & ado to do this, is it possible to use tsql to
get
sql server write the files from its oledb drivers, and would there be any
problems
doing this
in particular I do not know what arguments to pass to opendatasource to
create a new database.Hello Nick,
I understand that you 'd like to create Access database and access it from
SQL Server via TSQL directly. If it is not correct, let me know.
You may want to create linked server to Access database or use open
openquery/openrowset or four-part name syntax to query access database.
Please refer to teh following links for more informaitoin:
http://msdn2.microsoft.com/en-us/library/ms190479.aspx
287414 You cannot link tables into a Microsoft Access project by using an
ODBC Data Source Name (DSN)
http://support.microsoft.com/defaul...kb;EN-US;287414
As for creating database, it is not feasible to create directly from
opendatasource etc from SQL Server. You need to use adox/ado to do this as
you'v done.
317881 How to create an Access database by using ADOX and Visual C# .NET
http://support.microsoft.com/defaul...kb;EN-US;317881
If you have further questions, please let's know.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hello Nick,
I understand that you 'd like to create Access database and access it from
SQL Server via TSQL directly. If it is not correct, let me know.
You may want to create linked server to Access database or use open
openquery/openrowset or four-part name syntax to query access database.
Please refer to teh following links for more informaitoin:
http://msdn2.microsoft.com/en-us/library/ms190479.aspx
287414 You cannot link tables into a Microsoft Access project by using an
ODBC Data Source Name (DSN)
http://support.microsoft.com/defaul...kb;EN-US;287414
As for creating database, it is not feasible to create directly from
opendatasource etc from SQL Server. You need to use adox/ado to do this as
you'v done.
317881 How to create an Access database by using ADOX and Visual C# .NET
http://support.microsoft.com/defaul...kb;EN-US;317881
If you have further questions, please let's know.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||I have posted following question in sqlprogramming but have
not recieved a response so wondered whether I have posted
it correctly
https://msdn.microsoft.com/newsgrou...59-dfac726d711e
Please help,
since the introduction of SQL2005 the default mode for opening access mdb
databases with Openrowset appears to be in exclusive open mode
I wish to open the database in share mode so the other (non net) ADO threads
can open the database at the same time
I only wish to open the database in read only mode
and so to use the mode param
I have tried the following
SELECT COUNT(*)
FROM
OPENROWSET ( 'Microsoft.Jet.OLEDB.4.0',
'"Database = h:\alarmlogfiles\new folder (3)\alarms 04 feb
2007.mdb; Mode = Share Deny None;"'
, 'SELECT [ID],
[System],
[Group],
[Message],
[Acknowledged],
[AckReq],
[Duplicates],
[Group Name],
[Importance],
[Ms],
[Alarm Date],
[LastDupe],
[Viewers],
[Row_Date],
[Colours],
[Status] From [Alarms 08 Feb 2007] Where 1 = 0' )
but get the following error from sql2005 & sql 2000
Server: Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider
"Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)"
returned message "Could not find installable ISAM.".

SELECT CustomerID, CompanyName
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb';
'admin';'',Customers)
GO
but there is no opportunity here to add a open mode argument?
please help
best wishes nick
""privatenews"" wrote:
[vbcol=seagreen]
> Hello Nick,
> I understand that you 'd like to create Access database and access it from
> SQL Server via TSQL directly. If it is not correct, let me know.
> You may want to create linked server to Access database or use open
> openquery/openrowset or four-part name syntax to query access database.
> Please refer to teh following links for more informaitoin:
> http://msdn2.microsoft.com/en-us/library/ms190479.aspx
> 287414 You cannot link tables into a Microsoft Access project by using an
> ODBC Data Source Name (DSN)
> http://support.microsoft.com/defaul...kb;EN-US;287414
> As for creating database, it is not feasible to create directly from
> opendatasource etc from SQL Server. You need to use adox/ado to do this as
> you'v done.
> 317881 How to create an Access database by using ADOX and Visual C# .NET
> http://support.microsoft.com/defaul...kb;EN-US;317881
> If you have further questions, please let's know.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
>
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>
>|||Hello Nick,
Sorry for the late response and it seems there is sync issue in our
internal application for your account. I'v reported this issue to the
related team.
Per your question, I was able to reproduce the issue on my side. It seems
that we are not able to use provider or connection string in the
"datasource" parameter. It also doesn't work if I use provider string
directly:
SELECT CustomerID, CompanyName from
OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Data Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data
Source=C:\northwind.mdb;Passord=""',
'select * from customers')
I'v forward your feedback to the product team. In the meantime, I also
encourage you submit via the link below
http://lab.msdn.microsoft.com/produ...ck/default.aspx
Please let's know if you have any further feedback.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============