Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
| Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old July 20th, 2004, 09:59 AM
Registered User
 
Join Date: Jul 2004
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
quote:Originally posted by pgtips
 Maybe an example would help.

Say I have an Access db called "dest.mdb" which contains a table "OrderExtract" with 2 fields "OrderID", "ProductID" (both Long Integers) and I want to fill that table with all the OrderID/ProductID pairs from the Northwind db in SQL Server. Here's a SQL Server query which will do just that:

Code:
USE Northwind
GO

INSERT OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
  'Data Source="c:\path\dest.mdb";User ID=Admin;Password=;')...OrderExtract
SELECT OrderID, ProductID FROM [Order Details]
rgds
Phil
 
Old July 20th, 2004, 10:07 AM
Registered User
 
Join Date: Jul 2004
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I am using OpenDatasource function to retrieve data(filename) from a #table ( using cursor, one row(filename) at a time) and process the contents of the text file..say n rows in a file. If the file has no contents(no rows), i get an error. How do i work around this?? I would like to insert the processed contents of the file into a new table. hope the code celow helps..if the file has no contents then get an error :
OLE DB error trace [Non-interface error: OLE DB provider unable to process object, since the object has no columnsProviderName='Microsoft.Jet.OLEDB.4.0', Query=CPInvoiceOutPut2002_07_23_6_2#txt'].
Server: Msg 7357, Level 16, State 2, Line 1
Could not process object 'CPInvoiceOutPut2002_07_23_6_2#txt'. The OLE DB provider 'Microsoft.Jet.OLEDB.4.0' indicates that the object has no columns.

code:

drop table #testFTP


SELECT IDENTITY(int,1,1) as RowID, FILENAME, GETDATE() AS CREATED_ON into #testFTP FROM OPENDataSource('Microsoft.Jet.OLEDB.4.0',
 'Data Source = "D:\BuildAddOns\Scripts\InvoiceError";User ID=;Password=;Extended properties=Text')...filelist#txt where FILENAME LIKE 'CPInvoiceOutPut%'


Declare @RunSQL varchar(1000)
DECLARE @error_Insert int
DECLARE @error_select int
DECLARE @ROWID int
DECLARE @FILENAME Varchar(255)
DECLARE @DATETIME DATETIME

DECLARE CUR_testFTP CURSOR
FOR
Select t.RowID, t.FILENAME, t.CREATED_ON
from #testFTP t left outer join TESTFTP F
on t.FILENAME = F.FILENAME
where F.FILENAME is NULL

OPEN CUR_testFTP

FETCH NEXT FROM CUR_testFTP INTO @ROWID, @FILENAME, @DATETIME

--if ( @@FETCH_STATUS = 0) OR ( @@FETCH_STATUS <> 0 )
    CREATE TABLE #TESTFTPCOLUMNS ( col001 ntext )

WHILE @@FETCH_STATUS = 0

BEGIN

Select @RunSQL = 'INSERT INTO #TESTFTPCOLUMNS ' +
'SELECT * FROM OPENDataSource(''Microsoft.Jet.OLEDB.4.0'',
''Data Source = "D:\BuildAddOns\Scripts\InvoiceError";User ID=;Password=;Extended properties=Text'')...' +
Replace(FileName ,'.','#') From #testFTP Where RowID = CONVERT( VARCHAR(10),@ROWID)

SELECT @RunSQL

Exec(@RunSQL)

Select @error_Insert = @@ERROR

IF @error_Insert <> 0
    Begin
    Print 'Insert Error'
    declare @xFilename varchar(100)
    set @xFilename = 'The ' + @FILENAME + 'insert error has occured.'
    EXEC master.dbo.xp_startmail
    EXEC master.dbo.xp_sendmail @recipients = 'xyz@hotmail.com',
       @message = @xFilename,
          @subject = 'FTP Process Error'
    EXEC master.dbo.xp_stopmail
    End


INSERT TESTFTPCOLUMNS
Select Distinct
    left(CAST(col001 AS VARCHAR (1000)),1) InvoiceOrCreditNote ,rTrim(SUBSTRING(CAST(col001 AS VARCHAR (1000)),2,15)) + '-' +
    case SUBSTRING(CAST(col001 AS VARCHAR (1000)),17,1)
    When '0' Then SUBSTRING(CAST(col001 AS VARCHAR (1000)),18,1)
    Else SUBSTRING(CAST(col001 AS VARCHAR (1000)),17,2)
    End
    As ReferralNumber,
    case rTrim(SUBSTRING(CAST(col001 AS VARCHAR (1000)),19,1))
    When 'C' Then rTrim(SUBSTRING(CAST(col001 AS VARCHAR (1000)),20,19))
    Else rTrim(SUBSTRING(CAST(col001 AS VARCHAR (1000)),19,20))
    End
    as InvoiceNumber,
    GETDATE() AS CREATEDON
from dbo.#TESTFTPCOLUMNS
Where left(CAST(col001 AS VARCHAR (1000)),1)in ('I','C')
Order By InvoiceOrCreditNote,ReferralNumber

Select @error_select = @@ERROR

if @error_select <> 0
     begin
    Print 'file' + @FILENAME + 'Error'
    declare @yFilename varchar(100)
    set @yFilename = 'The ' + @FILENAME + 'process error has occured.'
     EXEC master.dbo.xp_startmail
    EXEC master.dbo.xp_sendmail @recipients = 'xyz@hotmail.com',
       @message = @yFilename,
          @subject = 'FTP Process Error'
    EXEC master.dbo.xp_stopmail
     end

INSERT INTO TESTFTP VALUES ( @FILENAME, @DATETIME )

FETCH NEXT FROM CUR_testFTP INTO @ROWID, @FILENAME, @DATETIME

TRUNCATE TABLE #TESTFTPCOLUMNS

END

DROP table #TESTFTPCOLUMNS

CLOSE CUR_testFTP
DEALLOCATE CUR_testFTP

 
Old January 4th, 2005, 05:12 PM
Registered User
 
Join Date: Jan 2005
Location: Allentown, PA, USA.
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I have more code if you need it, e.g. to create an MDB on the fly, but assuming that you have an existing MS Access 2K MDB, here a snippet of what I ended up doing to get this to work client-only:

[Ignore my Add_Status() app specific code, of course]

'All for SQL Server
'Public strServer As String
'Public strDb As String
'Public strUsr As String
'Public strPwd As String
'Public strTableName as String
'Dim strMDBTableName As String

            strMDBTableName = Format$(Now(), "ddmmmyyyy_hhmmss_") + strTableName

            lsSQL = "SELECT MDB_Temp_01.* "
            lsSQL = lsSQL + " INTO " + strMDBTableName + " IN '" + strMDBToCreate + "' "
            lsSQL = lsSQL + " FROM [odbc;DRIVER=SQL Server;"
            lsSQL = lsSQL + " SERVER=" + strServer
            lsSQL = lsSQL + ";"
            lsSQL = lsSQL + " UID=" + strUsr
            lsSQL = lsSQL + ";"
            lsSQL = lsSQL + " PWD=" + strPwd
            lsSQL = lsSQL + ";"
            lsSQL = lsSQL + " DATABASE=" + strDb
            lsSQL = lsSQL + "]." + strTableName
            lsSQL = lsSQL + " AS MDB_Temp_01"


            On Error Resume Next
            MDBConn.Execute lsSQL, , adExecuteNoRecords
            strHoldErr = Err.Description + "::" + Err.Source
            lngHoldErr = Err.Number
            On Error GoTo 0
            If lngHoldErr <> 0 Then
                strHoldErr = "Error [" + Format$(lngHoldErr) + "] - " + strHoldErr
                Add_Status (strHoldErr)
                Add_Status ("Unable to send requested table [" + strTableName + "] to " + strMDBToCreate)
                Add_Status ("Halting process")
                Exit Sub
            End If


Sam
 
Old March 21st, 2006, 08:35 AM
Registered User
 
Join Date: Mar 2006
Location: Mumbai, Maharashtra, India.
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi all.
I am working on an application which has app. 300000 record on SQL Server database, now I need to run a qurey to insert all record on a MS-Access table on client pc but it is not working. I have tried

INSERT OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source="\\Client_Machile_Name\D\sData.mdb";Jet OLEDB:Database Password=sa')...ITEM_MASTER SELECT ITEM_ID,MODEL_ID FROM ITEM_MASTER

It is giving error:[OLE/DB provider returned message: '\\Client_Machile_Name\D\sData.mdb' is not a valid path.

This same qurey is working if I am executing it on a server's MS-Access Database :

INSERT OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source="D:\sData.mdb";Jet OLEDB:Database Password=sa')...ITEM_MASTER SELECT ITEM_ID,MODEL_ID FROM ITEM_MASTER


Please healp me .






Similar Threads
Thread Thread Starter Forum Replies Last Post
INSERT DATE INTO SQL SERVER prasanta2expert ASP.NET 1.0 and 1.1 Basics 1 January 4th, 2007 08:27 AM
insert multiple record in sql server annie_stwg ASP.NET 2.0 Basics 4 September 19th, 2006 08:56 AM
Help about insert row in SQL Server 2000 dimeanel Beginning VB 6 3 January 19th, 2006 02:55 PM
How to insert a Picture in SQL Server using VB 6.0 swadhinm Pro VB Databases 0 September 21st, 2005 06:52 AM
ADO/SQL Server RecordSet and the OrderBy Property Dataman Access 3 February 9th, 2004 11:11 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.