Excel transfer to SQL using VB

[scoopthis]

Hi,
I have an application where I transfer an excel file to a table on SQL through an adp file. The excel file does change so I pass the name parameter from a cmd line. The excel file has a specific range defined of 20,000 records. Currently the process is using ac.DoCmd.TransferSpreadsheet acImport and it pulls in all 30,000 records even though there isn't data in all 20,000 records (lots of nulls). How can I get the process to only transfer the data where it isn't null?

Thanks.

[sashi]

hi there,

wll i feel the "null" problem is taken care of when it comes to comma delimited .txt, .csv format file.. give it a try.. good luck my fren..

[CaptainD]

VB can use the NZ() function, have you looked into that?

[Big K Hutch]

Where do you place this in the code?

[jojeh_computer]

Hello ,
I have an application in vb that must gets excell applicatiom and transfer data from excell worksheet to table of sql? how can i do this work? tanks for reply.

[mysql]

hi how can work with vb and mysql?
pls reply early

[Roytop]

Hi!
Use this little VBA code for uploading Excel data to MS SQL database.

Sub upload()
Set connAS400 = CreateObject("ADODB.Connection")
connAS400.Open (" Provider=SQLOLEDB.1;InitialCatalog=dbname;Server=s ervername;UID=user;PWD=password; ")
ConnOpenAS400 = True
connAS400.CommandTimeout = 100

For i = 1 To Workbooks(1).Worksheets(1).UsedRows.Count
query = "INSERT Roytop..test (text) VALUES ('" & Workbooks(1).Worksheets(1).Range("A" & i) & "')"
Set result = connAS400.Execute(query)
Next

result.Close
Set result = Nothing
End Sub

[Phrackers]

hi how can work with vb and mysql?
pls reply early

simply.. just install the mysql database and mysql odbc driver.. then in visual basic just call the odbc driver to connect into the mysql database..in then..u have now to code the basic query of sql.. thanks..

[xpcer]

simply.. just install the mysql database and mysql odbc driver.. then in visual basic just call the odbc driver to connect into the mysql database..in then..u have now to code the basic query of sql.. thanks..

can i have the codes? thanks before

[xpcer]

Hi!
Use this little VBA code for uploading Excel data to MS SQL database.

Sub upload()
Set connAS400 = CreateObject("ADODB.Connection")
connAS400.Open (" Provider=SQLOLEDB.1;InitialCatalog=dbname;Server=s ervername;UID=user;PWD=password; ")
ConnOpenAS400 = True
connAS400.CommandTimeout = 100

For i = 1 To Workbooks(1).Worksheets(1).UsedRows.Count
query = "INSERT Roytop..test (text) VALUES ('" & Workbooks(1).Worksheets(1).Range("A" & i) & "')"
Set result = connAS400.Execute(query)
Next

result.Close
Set result = Nothing
End Sub

For i = 1 To Workbooks(1).Worksheets(1).UsedRows.Count
query = "INSERT Roytop..test (text) VALUES ('" & Workbooks(1).Worksheets(1).Range("A" & i) & "')"
Set result = connAS400.Execute(query)
Next

cannot execute....

[CaptainD]

For i = 1 To Workbooks(1).Worksheets(1).UsedRows.Count
query = "INSERT Roytop..test (text) VALUES ('" & Workbooks(1).Worksheets(1).Range("A" & i) & "')"
Set result = connAS400.Execute(query)
Next

cannot execute....

Did you change the values he gave you to match your configurations?