txt file to sqlserver

[Directlinq]

What piece of vba code would i need to add records from a comma delimited txt file to my sql server.

i need to use access to ad the records

Im really struggling here if anybody can help

Many Thanks

 

[DCrake]

Is the SQL linked to Access via ODBC or are you using ADODB? Going off line now will pick this up tomorrow lunch. Have to go for a blood test in the morning. Don't like needles.

David

 

[gemma-the-husky]

assuming your app links to the sql server backend

then i would do

a) docmd.transfertext to import the table into your front end
b) do any validation tests you need
c) then have a query(s) that adds the data to appropriate linked tables.

doing it this way means you can do the important validation and verification step - you dont have to try and get the data nito the backend directly

 

[Directlinq]

hi no validation tests are needed.
Im trying the docmd.transfertext route now with little success.
The wierd thing is im importing 2 txt files 1 into artists table and 1 into video table, when i run my vba code, which ever is first succeeds and the second fails. Any order the second fails. With an error about primary keys ect. But why? if i change the order it did not have a problem with primary keys then?

here is all my code incase some life saver can help me out.

Dim SH As SHFILEOPSTRUCT
Dim Artistfile As String
Dim Videofile As String
Artistfile = Dir$("D:\Database\Artist*.txt")
Videofile = Dir$("D:\Database\Video*.txt")

Answer = MsgBox("Please Put The DVD in the drive and press ok", vbYesNo + vbExclamation + vbDefaultButton2, "Is DVD in Drive?")
If Answer = vbYes Then
Me.Label2.Visible = True
'Copy all mpeg files from dvd to temp folder
With SH
.fFlags = FOF_CREATEPROGRESSDLG 'flag for progressbar
.wFunc = FO_COPY 'to copy
.pFrom = "D:\*.MPG" 'source
.pTo = "F:\MediaLib\Video"
End With

Call SHFileOperation(SH)
GoTo importt
End If
Exit Sub
importt:

DoCmd.TransferText acImportDelim, "", "dbo_tblVideo", "D:\Database\" & Videofile, False
DoCmd.TransferText acImportDelim, "", "dbo_tblArtist", "D:\Database\" & Artistfile, False
Me.Label2.Visible = False
MsgBox "Media Update Complete Press Ok to Start"

 

[DCrake]

Can you post a small copy of each txt file that is about to be imported please.

David

 

[Directlinq]

As for the linked tables i used the wizard but i think it uses ODBC

Here is the Video*.txt

2689,171,3,184,10,,"VDT028543.MPG","RELAX (REMIX)","","2009",1,"0",05/11/2009 10:38:06,0,0,"VDT028543",,"",""
2690,1225,3,98,10,,"VDT028544.MPG","ANYWAY","","2009",1,"0",05/11/2009 10:38:07,0,0,"VDT028544",,"",""
2691,759,3,167,7,,"VDT028545.MPG","DIRTEE CASH","","2009",1,"0",05/11/2009 10:38:08,0,0,"VDT028545",,"",""
2692,1226,3,167,7,,"VDT028546.MPG","BOUNCE","","2009",1,"0",05/11/2009 10:38:08,0,0,"VDT028546",,"",""
2693,1227,3,148,10,,"VDT028547.MPG","IN YOUR SHOES","","2009",1,"0",05/11/2009 10:38:09,0,0,"VDT028547",,"",""
2694,795,3,98,10,,"VDT028548.MPG","FACE DROP","","2009",1,"0",05/11/2009 10:38:09,0,0,"VDT028548",,"",""
2695,1228,3,74,8,,"VDT028549.MPG","WHATCHA SAY","","2009",1,"0",05/11/2009 10:38:09,0,0,"VDT028549",,"",""

Here is the Artist*.txt

1225,"ARMAND VAN HELDEN + DUCKSAUCE","",""
1226,"MSTRKRFT FT. NORE","",""
1227,"BEVERLEY KNIGHT & CHIPMUNK","",""
1228,"JASON DERULO","",""
1229,"DIONNE BROMFIELD","",""
1230,"LITTLE BOOTS","",""
1231,"KE$HA","",""

Thank you so much for helping

 

[DCrake]

As I can see there is no column headings in the files. I would suggest you create two import specifications using the import wizard and refer to the saved specs during your tranfertext routine.

David

 

[Directlinq]

I have tried that too and the same thing happens the first DoCmd.TransferText line gets put into the table and the second one fails.
I have also tried including the column headings and change the false to true. But still the same. ~Im confused

 

[DCrake]

Have quickly knocked together a sample for you to look at. I have created two txt files based on the samples you posted.

Take a look at the conveted module to view syntax

Test with your files. As you can see I have created 2 import specs that import into existing tables if you change the fieldnames and data type to suit this should work.

David

 

[Directlinq]

Hi Thanks again for your help.

I noticed that your primary key in the video and artist table is an extra field. My first number in both the artist and video tables are the primary keys. But they need to be what i specify in the text file or the whole thing wont work. If you try importing the video text file again with the first number being the primary key it works but then try and import the Artist (with first number primary key) and it fails. Close access open it up again import artist and it works,then import video and it fails.

 

[DCrake]

Then change the spec to suit your needs. This was only a working example to get you up and running. Because the text files do not come with column headings it was impossible to decypher what the contents meant.

David

 

[Directlinq]

Hi I have copyed my 2 complete sql tables so i have 2 non linked tables with exactly the same data as on my sql server.
And it works like a treat.
But when i change the table names in the vba to the linked tables only one file gets imported and the second file fails.

So your code does work on my tables with the exact same formatting as on my sql server. But using it on linked tables only one gets imported.

Any ideas?

 

[DCrake]

Ok Split the function into two seperate functions for arguments sake. Run the one that works. Wait then run the one that fails.

Question: do they both run ok? Is it the same the one each time that fails? if you reverse your import order does it affect it? Could be due to relationships that exist and you are trying to impor the child before the parent. Children cannot be orphans? Its a case of trial and error.

David

 

[Directlinq]

Ok Ive just split the function into 2, 1 for artist update and 1 for video update.
Take 1
i ran the artist update and it worked
i ran the video update and it failed.

Deleted the newly added artist records from the table
(Restart access)

Take 1
i ran the video update and it worked.
i ran the artist update and it failed.

It seems by restarting access it looses the link to the first table i access. (just a thought)
Ahhhhhhhhhh

 

[DCrake]

When you say it fails, what is actually falling over?

David