Replies

SQL Manager in the server or from a PC. You can then choose the fields you require.
Copy the output into Excel and save as CSV.

cheers, Paul

no SQL Manager only code vb6 or vba for excel

Do you need to copy the table or just the data? ie does the table already exist in the second database?

– table and the data

To copy the data you can use two ADO connections.

Moving through the recordset in the first database and appending it to the second.

To create the table itself, if you need to, then do you want to do this in code as well or manually.

Manually – in Management Studio, right click on the table, select Script Table As > Create To > New Query Editor WIndow.

This will create a query for you to run on the second database to create the same table structure.

Or if you want to create the table in the second database using code then you can use the query you just created to build DDL code that you can run against your ADO connection.

So to answer your question, we really need to know more about what you need to do and how much you already know

eg how much do you know about using ADO recordsets?

Can you create the tables in Management Studio? If not then are you familiar with DDL?
– no
– no

Could try this

Function top and then just an example of using it in a sub below

Note it also works on a sheet as a function

Code:

Function FindDateDiffDays(varStart, varEnd) As Long

Dim lngDiff As Long, dteStart As Date, dteEnd As Date

'NOTE there is NO error trapping in this
Application.Volatile

dteStart = DateSerial(Left(varStart, 4), Mid(varStart, 5, 2), Right(varStart, 2))
dteEnd = DateSerial(Left(varEnd, 4), Mid(varEnd, 5, 2), Right(varEnd, 2))

lngDiff = (dteEnd - dteStart) + 1

FindDateDiffDays = lngDiff

End Function

Sub ExampleDD()

Dim varStart, varEnd, lngDays As Long


varStart = 20100405
varEnd = 20100409

lngDays = FindDateDiffDays(varStart, varEnd)

MsgBox "Difference is.... " & lngDays

End Sub

NATURALLY WORK!
Tks.

I am sure there are several solutions to this

Here is a sub with an example

You could make it into a Function and pass the Date

Code:

Sub CheckDate()

Dim varDate, varRealDate, strDay As String

varDate = "20100405"
varRealDate = DateSerial(Left(varDate, 4), Mid(varDate, 5, 2), Right(varDate, 2))
strDay = Format(varRealDate, "ddd")

'You may need to change this to use the Local Language equivalent of Monday
If strDay = "Mon" Then
    MsgBox "Monday"
Else
    MsgBox "Not a Monday " & strDay
End If


End Sub

OR as a Function that returns True or False

Code:

Function CheckMonday(varDate) As Boolean

Dim varRealDate, strDay As String

Application.Volatile
varRealDate = DateSerial(Left(varDate, 4), Mid(varDate, 5, 2), Right(varDate, 2))
strDay = Format(varRealDate, "ddd")

'You may need to change this to use the Local Language equivalent of Monday
If strDay = "Mon" Then
    CheckMonday = True
Else
   CheckMonday = False
End If

End Function

work perfect!

sorry but how to calculate datediff in days between 20100405 and 20100409 …in this case is 5
Tks.

Generally, you want to use the NoMatch property after you do a Find; that is: If rs1.NoMatch = False then …

error: member or method not fiund
in line rs.NoMatch

Ah.. But you didn’t say that.

Yes it does

In that case you probably need to open the recordset outside of the loop

Code:

'Outside your Loop

set rst=New ADODB.Recordset
strSQL="SELECT * FROM tblTest "
rst.Open strSQL,conn, adOpenStatic, adLockReadOnly 

'Then  inside your loop after var is set

rst.Movefirst
strWhere = "[Test]='" & var & "'"
rst.Find strWhere
If rst.EOF Then
	Var_Finded="No Matching Entry Found"
Else
        Var_Finded=rst![Test1]
End If

'Whatever else you want to do

'End of Your Loop

rst.Close
set rst=Nothing

This opens the Recordset Up Once with all records and uses the FIND Method to Look for the Specific Record
It is important each time through the loop to set the Record Pointer Back to the First Record

similar:

‘Outside your Loop

set rst=New ADODB.Recordset
strSQL=”SELECT * FROM tblTest ”
rst.Open strSQL,conn, adOpenStatic, adLockReadOnly

‘Then inside your loop after var is set

for each cells in my range
var = cells….

rst.Movefirst
strWhere = “[Test]='” & var & “‘”
rst.Find strWhere
If rst.EOF Then
Var_Finded=”No Matching Entry Found”
Else
Var_Finded=rst![Test1]
End If

‘Whatever else you want to do

‘End of Your Loop

next cells

rst.Close
set rst=Nothing

I assume that this is NOT running from Access in which case DLOOKUP is not available to you.
so you will need to reference a table eg tblTest via an ADO Recordset

[/size][/size][/font]
Something Like this ought to work.[/size]
[/size][/font]

[Code]
set rst=New ADODB.Recordset
strSQL=”SELECT * FROM tblTest WHERE [Test]='” & var & “‘”
rst.Open strSQL,conn, adOpenStatic, adLockReadOnly
If Not rst.EOF Then
Var_Finded=rst![Test1]
Else
Var_Finded=”No Matching Entry Found”
End If
rst.Close
Set rst=Nothing
[/Code]

ok… tks for code.
But the var is dinamic…. i fill var from a loop in range in Excel colum.
If i use your code is required to open and closed recordset each time, or not?

Place the following declaration at the top of the code module:

Code:

Public Declare Function SetCurrentDirectory _
  Lib "kernel32" Alias "SetCurrentDirectoryA" _
  (ByVal lpPathName As String) As Long

You can now use

Code:

Dim Vfile
SetCurrentDirectory "\myserverdir1dir2"
Vfile = Application.GetOpenFilename("CSV files,*.csv")

… but instead to open dialogbox, i know the name of file, how to set Vfile?

For example:

Code:

	Dim DATA_TIPOLOGIA As String
	DATA_TIPOLOGIA = "06/01/2010"
	DATA_TIPOLOGIA = DateAdd("m", -1, DateValue(DATA_TIPOLOGIA))
	MsgBox DATA_TIPOLOGIA

work! tks.

sorry for some reason i’m missing a blank between the quotes in my fragment – each of the function calls ‘InStr(strTemp, ” “)’ should have 2 blanks between the quotes, not 1….. if there’s only 1, all spaces will be removed.

:-/

Tks to thr all…!!!
Resolaved with one or more solution in this post.

You can use:

Code:

My_Var = Application.Trim(My_Var)

… but if i am not wrong, the line code delete all blank space, or not?
In effect i want to reduce the number of blank space in one only.
example:
if the string contain 4 balnk space reduce the current number of blank space in one blank space
if the string contain 9 balnk space reduce the current number of blank space in one blank space

Longs don’t have decimal places – they are integers. Did you mean Double?

sorry…. Double, naturally.

Longs don’t have decimal places – they are integers. Did you mean Double?

sorry…. Double, naturally.

Longs don’t have decimal places – they are integers. Did you mean Double?

sorry…. Double, naturally.

Longs don’t have decimal places – they are integers. Did you mean Double?

sorry…. Double, naturally.