Board index » VBA

All times are UTC



 Insert contents of text file into table using ado connection 
Author Message
Richa
#1 / 9
 Insert contents of text file into table using ado connection

I am trying to insert the contents of a text file into a table, but
keep coming up with an error (syntax error in insert to statement)
message when I run it from a module, even though when I paste the sql
into the query window of the relevant database it runs smoothly.
Wondering if there's a problem with the ado connection

Here is my code:-

Public Sub subImportTextFile()
Dim cnnInterm As ADODB.Connection, cnnImport As ADODB.Connection
Dim strSQL As String, strInterm As String, strImport As String

strImport = DLookup("[FileLocation]", "tblFileLocations",
"LocationID=1")
strInterm = DLookup("[FileLocation]", "tblFileLocations",
"LocationID=2")

Set cnnImport = New ADODB.Connection
Set cnnInterm = New ADODB.Connection

cnnImport.Open "Provider=Microsoft.Jet" & _
        ".OLEDB.4.0;Data Source=" & fDBDir(strImport) & _
        ";Extended Properties=""text;HDR=YES;" & _
        "FMT=Delimited"""

cnnInterm.Open "Provider=Microsoft.Jet" & _
        ".oledb.4.0;" & _
        "Data Source=" & strInterm

strSQL = "INSERT INTO YTD (SiteID, ClientID, ProductID, Amount)" & _
        " SELECT SiteID, ClientID, ProductID, Amount" & _
        " FROM [Text;Database=" & fDBDir(strImport) & ";HDR=YES]." &
fDBName(strImport)

cnnInterm.Execute strSQL    ' Error occurs here.  

Any help appreciated

Richard



Sat, 07 Feb 2004 23:11:11 GMT  
Dev Ashis
#2 / 9
 Insert contents of text file into table using ado connection

Quote:

> cnnInterm.Execute strSQL    ' Error occurs here.  

Why not post the contents of strSQL?

 -- Dev



Sun, 08 Feb 2004 19:58:12 GMT  
Richa
#3 / 9
 Insert contents of text file into table using ado connection

Quote:

> Why not post the contents of strSQL?

OK, I'm now doing this the other way around i.e. running the action
from the text file connection.  I am now met with a different error
message in code (although it works if I paste the contents of the sql
string into the query window of the target database and run it from
there).  I'm also using a schema file.

I'm now met with a "no value given for one or more required
parameters" error.

However if I bypass (delete) the schema file I get the message:-

"The field is too small to accept the amount of data you attempted to
add.  Try inserting or pasting less data."

This happens when running on the ado connection.  It doesn't happen
just using the query window.

Anyone help, Please?

Richard

BTW Disclaimer:  I've taken over the running of this database, the
field naming wasn't my idea.

Dim cnnImport As ADODB.Connection
Dim strSQL As String

Set cnnImport = New ADODB.Connection

cnnImport.Open "Provider=Microsoft.Jet" & _
       ".OLEDB.4.0;Data Source=" & c:\Apps\ & _
       ";Extended Properties=""text;HDR=YES;" & _
       "FMT=Delimited"""

strSQL = "INSERT INTO YTD ([Site], [Gross], [Tare], [Net], [T], [TO],
[D], [Ticketo], [Reg], [Client], [CName]," & _
            " [Prod], [PR], [Dest], [Des], [A], Comments2,
[RoundCode], [RoundDescription]," & _
            " [EmergencyTicketNo], [InOrOut])" & _
            " IN '" & c:\Apps\Intermed.mdb & "'" & _
        " SELECT [Weighbridge], [Gross Weight], [Tare Weight], [Net
Weight], [Date/Time In]," & _
            " [Date/Time Out], [Date/Time Load], [Ticket Number],
[Registration], [Client Code]," & _
            " [Client Name], [Product Code], [Product Description],
[Destination Code]," & _
            " [Destination Description], [Comments1], [Comments2],
[Round Code], [Round Description]," & _
            " [Emergency Ticket Number], [In Or Out]" & _
        " FROM [" & fDBName(strImport) & "]"

cnnImport.Execute strSQL



Mon, 09 Feb 2004 01:17:28 GMT  
Brendan Reynold
#4 / 9
 Insert contents of text file into table using ado connection
I don't use ADO much, but shouldn't c:\Apps\ be *inside* the quotes?

--
Brendan Reynolds

http://www11.ewebcity.com/brenreyn


Quote:

> > Why not post the contents of strSQL?

> OK, I'm now doing this the other way around i.e. running the action
> from the text file connection.  I am now met with a different error
> message in code (although it works if I paste the contents of the sql
> string into the query window of the target database and run it from
> there).  I'm also using a schema file.

> I'm now met with a "no value given for one or more required
> parameters" error.

> However if I bypass (delete) the schema file I get the message:-

> "The field is too small to accept the amount of data you attempted to
> add.  Try inserting or pasting less data."

> This happens when running on the ado connection.  It doesn't happen
> just using the query window.

> Anyone help, Please?

> Richard

> BTW Disclaimer:  I've taken over the running of this database, the
> field naming wasn't my idea.

> Dim cnnImport As ADODB.Connection
> Dim strSQL As String

> Set cnnImport = New ADODB.Connection

> cnnImport.Open "Provider=Microsoft.Jet" & _
>        ".OLEDB.4.0;Data Source=" & c:\Apps\ & _
>        ";Extended Properties=""text;HDR=YES;" & _
>        "FMT=Delimited"""

> strSQL = "INSERT INTO YTD ([Site], [Gross], [Tare], [Net], [T], [TO],
> [D], [Ticketo], [Reg], [Client], [CName]," & _
>             " [Prod], [PR], [Dest], [Des], [A], Comments2,
> [RoundCode], [RoundDescription]," & _
>             " [EmergencyTicketNo], [InOrOut])" & _
>             " IN '" & c:\Apps\Intermed.mdb & "'" & _
>         " SELECT [Weighbridge], [Gross Weight], [Tare Weight], [Net
> Weight], [Date/Time In]," & _
>             " [Date/Time Out], [Date/Time Load], [Ticket Number],
> [Registration], [Client Code]," & _
>             " [Client Name], [Product Code], [Product Description],
> [Destination Code]," & _
>             " [Destination Description], [Comments1], [Comments2],
> [Round Code], [Round Description]," & _
>             " [Emergency Ticket Number], [In Or Out]" & _
>         " FROM [" & fDBName(strImport) & "]"

> cnnImport.Execute strSQL



Mon, 09 Feb 2004 04:12:58 GMT  
Richar
#5 / 9
 Insert contents of text file into table using ado connection
Yep, Sorry I'm actually using a string variable for this.  And yes I
am using the quotes.

Richard

On Wed, 22 Aug 2001 21:12:58 +0100, "Brendan Reynolds"

Quote:

>I don't use ADO much, but shouldn't c:\Apps\ be *inside* the quotes?



Mon, 09 Feb 2004 09:03:43 GMT  
Brendan Reynold
#6 / 9
 Insert contents of text file into table using ado connection
It might help if you copied and pasted the actual code, David - otherwise,
we can end up debugging typos in the newsgroup posting, instead of debugging
the code. :-)

--
Brendan Reynolds


Quote:
> Yep, Sorry I'm actually using a string variable for this.  And yes I
> am using the quotes.

> Richard

> On Wed, 22 Aug 2001 21:12:58 +0100, "Brendan Reynolds"

> >I don't use ADO much, but shouldn't c:\Apps\ be *inside* the quotes?



Mon, 09 Feb 2004 17:10:26 GMT  
Richa
#7 / 9
 Insert contents of text file into table using ado connection
Just for clarity (and urgent attention - PLEASE HELP?!) I thought I'd
repost the sql.  Is there anything wrong with this?

Dim cnnInterm As ADODB.Connection, cnnImport As ADODB.Connection
Dim strDb As String, strSQL As String, strInterm As String, strImport
As String

'   Code to create schema.ini file from destination table

Set cnnImport = New ADODB.Connection
Set cnnInterm = New ADODB.Connection

cnnImport.Open "Provider=Microsoft.Jet" & _
       ".OLEDB.4.0;Data Source=" & fDBDir(strImport) & _
       ";Extended Properties=""text;HDR=YES;" & _
       "FMT=Delimited"""

cnnInterm.Open "Provider=Microsoft.Jet" & _
        ".OLEDB.4.0;" & _
        "Data Source=" & strInterm

strSQL = "INSERT INTO YTD ([Site], [Gross], [Tare], [Net], [T], [TO],"
& _
             " [D], [Ticketo], [Reg], [Client], [CName], [Prod],
[PR]," & _
             " [Dest], [Des], [A], [Comments2], [RoundCode]," & _
             " [RoundDescription], [EmergencyTicketNo], [InOrOut])" &
_
         " IN 'C:\Apps\Intermed.mdb'" & _
         " SELECT [Weighbridge], [Gross Weight], [Tare Weight]," & _
             " [Net Weight], [Date/Time In], [Date/Time Out]," & _
             " [Date/Time Load], [Ticket Number], [Registration]," & _
             " [Client Code], [Client Name], [Product Code]," & _
             " [Product Description], [Destination Code]," & _
             " [Destination Description], [Comments1], [Comments2]," &
_
             " [Round Code], [Round Description]," & _
             " [Emergency Ticket Number], [In Or Out] FROM [New.txt]"

cnnImport.execute strsql



Mon, 09 Feb 2004 19:54:37 GMT  
Bob Barrow
#8 / 9
 Insert contents of text file into table using ado connection
Could you do a debug.print strsql and post that as well? You should be able
to copy the result of debug.print out of the Debug window and paste it into
the SQL View of a query builder window and run it without modification.

Bob Barrows


Quote:
> Just for clarity (and urgent attention - PLEASE HELP?!) I thought I'd
> repost the sql.  Is there anything wrong with this?

> Dim cnnInterm As ADODB.Connection, cnnImport As ADODB.Connection
> Dim strDb As String, strSQL As String, strInterm As String, strImport
> As String

> '   Code to create schema.ini file from destination table

> Set cnnImport = New ADODB.Connection
> Set cnnInterm = New ADODB.Connection

> cnnImport.Open "Provider=Microsoft.Jet" & _
>        ".OLEDB.4.0;Data Source=" & fDBDir(strImport) & _
>        ";Extended Properties=""text;HDR=YES;" & _
>        "FMT=Delimited"""

> cnnInterm.Open "Provider=Microsoft.Jet" & _
>         ".OLEDB.4.0;" & _
>         "Data Source=" & strInterm

> strSQL = "INSERT INTO YTD ([Site], [Gross], [Tare], [Net], [T], [TO],"
> & _
>              " [D], [Ticketo], [Reg], [Client], [CName], [Prod],
> [PR]," & _
>              " [Dest], [Des], [A], [Comments2], [RoundCode]," & _
>              " [RoundDescription], [EmergencyTicketNo], [InOrOut])" &
> _
>          " IN 'C:\Apps\Intermed.mdb'" & _
>          " SELECT [Weighbridge], [Gross Weight], [Tare Weight]," & _
>              " [Net Weight], [Date/Time In], [Date/Time Out]," & _
>              " [Date/Time Load], [Ticket Number], [Registration]," & _
>              " [Client Code], [Client Name], [Product Code]," & _
>              " [Product Description], [Destination Code]," & _
>              " [Destination Description], [Comments1], [Comments2]," &
> _
>              " [Round Code], [Round Description]," & _
>              " [Emergency Ticket Number], [In Or Out] FROM [New.txt]"

> cnnImport.execute strsql



Mon, 09 Feb 2004 20:38:49 GMT  
Richar
#9 / 9
 Insert contents of text file into table using ado connection
Ok, I've finally sorted this one out.  It seems that the field sizes
on my source text file and destination databases were different.  Even
though all the data of certain source fields were within the limits of
the destination database, it seemed to trip over itself.

For instance one field DestinationCode in the destination db had a
field size of 50, and although no data from the text file went over 50
characters, it was reporting a field size of 255 and this was enough
to stop the process.

Thanks all for your help.

Richard

On Thu, 23 Aug 2001 08:38:49 -0400, "Bob Barrows"

Quote:

>Could you do a debug.print strsql and post that as well? You should be able
>to copy the result of debug.print out of the Debug window and paste it into
>the SQL View of a query builder window and run it without modification.

>Bob Barrows



>> Just for clarity (and urgent attention - PLEASE HELP?!) I thought I'd
>> repost the sql.  Is there anything wrong with this?

>> Dim cnnInterm As ADODB.Connection, cnnImport As ADODB.Connection
>> Dim strDb As String, strSQL As String, strInterm As String, strImport
>> As String

>> '   Code to create schema.ini file from destination table

>> Set cnnImport = New ADODB.Connection
>> Set cnnInterm = New ADODB.Connection

>> cnnImport.Open "Provider=Microsoft.Jet" & _
>>        ".OLEDB.4.0;Data Source=" & fDBDir(strImport) & _
>>        ";Extended Properties=""text;HDR=YES;" & _
>>        "FMT=Delimited"""

>> cnnInterm.Open "Provider=Microsoft.Jet" & _
>>         ".OLEDB.4.0;" & _
>>         "Data Source=" & strInterm

>> strSQL = "INSERT INTO YTD ([Site], [Gross], [Tare], [Net], [T], [TO],"
>> & _
>>              " [D], [Ticketo], [Reg], [Client], [CName], [Prod],
>> [PR]," & _
>>              " [Dest], [Des], [A], [Comments2], [RoundCode]," & _
>>              " [RoundDescription], [EmergencyTicketNo], [InOrOut])" &
>> _
>>          " IN 'C:\Apps\Intermed.mdb'" & _
>>          " SELECT [Weighbridge], [Gross Weight], [Tare Weight]," & _
>>              " [Net Weight], [Date/Time In], [Date/Time Out]," & _
>>              " [Date/Time Load], [Ticket Number], [Registration]," & _
>>              " [Client Code], [Client Name], [Product Code]," & _
>>              " [Product Description], [Destination Code]," & _
>>              " [Destination Description], [Comments1], [Comments2]," &
>> _
>>              " [Round Code], [Round Description]," & _
>>              " [Emergency Ticket Number], [In Or Out] FROM [New.txt]"

>> cnnImport.execute strsql



Tue, 10 Feb 2004 04:03:32 GMT  
 
 Page 1 of 1
  [ 9 post ] 

 Relevant Pages 

1. Insert fields in or more tables in visual basic with ADO connection

2. Comma delimited text file to DB table using ADO

3. convert or inserting content the text file to MS Access database

4. i want to insert a print button on the table and print the table content

5. Using Contents of text box on form for Table name

6. Help with insert into SQL65 table using ADO

7. Copy text file contents to database table?

8. Copy text file contents to database table?

9. ADO connection using Foxpro table (DBF) - SQL

10. Write contents of listbox to text file/populate listbox from text file

11. Reading a comma delimited text file and inserting the values into SQL Server table

12. Inserting data into mutliple tables using SQL Insert Command

  

 
Powered by phpBB® Forum Software