PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197
[RESOLVED] Syntax help for Access DB Query-VBForums
Results 1 to 7 of 7

Thread: [RESOLVED] Syntax help for Access DB Query

  1. #1

    Thread Starter
    New Member
    Join Date
    May 2018
    Posts
    3

    Resolved [RESOLVED] Syntax help for Access DB Query

    Hi,

    I am assuming/hoping there is an easy fix for this but no matter what I try or search I cannot get this to work. I am importing .txt files into an Access 2003 database. The headers are contained as the first line of the .txt file. I have a relatively simple query that works correctly, but I wish to automate the process with my VB,net code due to the number of files. Therefore I would like to add a file location, which is stored as a string, to the query. However, when I do this the query fails.

    I would also like to know if this imported .txt file has to be in the same location as the DB. At the moment if this is not the case the query also fails.

    I have attached a simplified version of my query with only the relevant lines included to try and help explain what I mean.

    Thanks in advance for any assistance or input.
    Cheers
    Jeff

    Code:
    Imports System
    Imports System.IO
    Imports System.Data.OleDb
    Database Location
    Code:
    Dim DB_Connect As String = "provider=microsoft.jet.oledb.4.0;data source = E:\Documents\My_DB.mdb"
    Code:
    Dim TableHeaders As String = "Field1, Field2, Field3, Field4"
    Dim ColumnHeaders As String = " Data1, Data2, Data3, Data4"
    Dim FileName As String = "DataFile.txt"
    This query works as long as the DataFile.txt is stored in the same location as the DB eg in E:\Documents
    Code:
    Dim MyQuery As String = "INSERT INTO Table1 (Field1, Field2, Field3, Field4) SELECT Data1, Data2, Data3, Data4 FROM [Text;HDR=yes;FMT=Delimited;DATABASE=E:\Documents\;].[DataFile.txt]"
    This query works as long as the DataFile.txt is stored in the same location as the DB eg in E:\Documents
    Code:
    Dim MyQuery As String = "INSERT INTO Table1 (" & TableHeaders & ") Select " & ColumnHeaders & " FROM [Text;HDR=yes;FMT=Delimited;DATABASE=E:\Documents\;].[DataFile.txt]"
    This query does not work
    Code:
    Dim MyQuery As String = "INSERT INTO Table1 (" & TableHeaders & ") Select " & ColumnHeaders & " FROM [Text;HDR=yes;FMT=Delimited;DATABASE=E:\Documents\;].[" & FileName & "]"

  2. #2
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    99,783

    Re: Syntax help for Access DB Query

    Maybe you could look at, and show us, the contents of the actual String rather than the code that builds it.

    Also, I suggest that you look into using the String.Format method or string interpolation for readability.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  3. #3

    Thread Starter
    New Member
    Join Date
    May 2018
    Posts
    3

    Re: Syntax help for Access DB Query

    Hi,

    Not really sure if this is what you are asking for, but the contents of the real files have many columns of data (too many to list here), so I just tried to make a simple subset of for ease of reading.

    But for the simple test file DataFile.txt I have shown above and used in my example:
    DB Field Names Field1, Field2, Field3, Field4 -
    Text File Line 1 Field1, Field2, Field3, Field4
    Text File Line 2 aaaaa, bbbbb, ccccc, ddddd

    All DB fields set to text. It only fails when i use .[" & FileName & "]" instead of of the actual test file name attached as .[DataFile.txt]" This is the same for the test file or the real files.

    As for the String.Format I may look at that once i get this query sorted although i normally use line breaks to make it easy to read. But always happy to try something new.


    Jeff

  4. #4
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    99,783

    Re: Syntax help for Access DB Query

    Not the contents of the file. This code builds a String:
    vb.net Code:
    1. Dim MyQuery As String = "INSERT INTO Table1 (" & TableHeaders & ") Select " & ColumnHeaders & " FROM [Text;HDR=yes;FMT=Delimited;DATABASE=E:\Documents\;].[" & FileName & "]"
    If using that String doesn't do as you expect then obviously that String doesn't contain what you expect. Did you think to look to see what it DOES contain and compare that to a String that DOES work?
    As for the String.Format I may look at that once i get this query sorted although i normally use line breaks to make it easy to read.
    You should look at it now because if you are making a mistake because you're misreading your code (it has happened MANY times) then it may just make the problem go away.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  5. #5

    Thread Starter
    New Member
    Join Date
    May 2018
    Posts
    3

    Re: Syntax help for Access DB Query

    Thank-you for your attempted help. But did you read the last two lines of my code that i posted in my first post? It shows exactly what I was trying to do and the fact the query, just like many others i have written work fine. It also includes everything required to build the query. Just for this there may be a syntax error that I cannot find information on. eg. you need to enclose a sting in " ' ". All I was just trying to remove 2 lines of code by using this modification to my working query.

    I will mark this as resolved.

  6. #6
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    99,783

    Re: Syntax help for Access DB Query

    Quote Originally Posted by Jeffro.68 View Post
    But did you read the last two lines of my code that i posted in my first post?
    Yes. Yes I did.
    Quote Originally Posted by Jeffro.68 View Post
    Just for this there may be a syntax error that I cannot find information on
    And yet you decided to NOT actually look at the result of that code, i.e. the String it builds, to see what it contains to see what might be wrong with what it contains.

    I give up. You can lead a horse to water...
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  7. #7
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    6,925

    Re: [RESOLVED] Syntax help for Access DB Query

    Indeed. What JM is asking you is important. We can see that this builds a string:-
    Code:
    Dim MyQuery As String = "INSERT INTO Table1 (" & TableHeaders & ") Select " & ColumnHeaders & " FROM [Text;HDR=yes;FMT=Delimited;DATABASE=E:\Documents\;].[" & FileName & "]"
    We can guess at what the string will look like, but we cannot know what it looks like. And without knowing we can't tell you what's wrong with the syntax.

    Put a break point on that like of code and run your application using the debug option. when it pauses on that line of code, cut and paste it into the immediate window preceded by a ?. That will tell you exactly what your connection string looks like. 9 times out of 10 the problem will become immediately obvious. If it isn't obvious, post back and we'll help you dig deeper.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width