Upsizing acces to SQL

[fiu88]

Does anyone have experience with this for e-comm? Is it relatively trouble free? o should I avoid it and just switch to a mysql?

[woop01]

It's not impossible for somebody who doesn't know what they're doing but be prepared to spend a night figuring out what exactly is wrong with some things. It was WELL worth the move for us. I cannot imagine running a high use e-commerce site from Access.

[mattglet]

Kind of related:
[webmasterworld.com ]

[Zaphod Beeblebrox]

Create a module in your Access database and copy/paste the following code into it. When you run it, it will create a SQL query in 'CreateDB.SQL' that you can run on your SQL Server to create and populate all the tables in your access database.

Check the debug window afterwards for any messages reading "Unknown: [number]". These constitute Access datatypes that haven't been added to the "Select Case F.Type". Simply add them and run it again.

Option Compare Database
Sub Main()
 Dim Db
 Dim Td
 Dim F
 Dim B
 Dim I, J
 Dim Rs
 Dim P
 Dim SQL
 Dim lstr
 
 Set Db = CurrentDb
 J = FreeFile
 P = Left(CurrentDb.Name, InStrRev(CurrentDb.Name, "\"))
 Open P & "CreateDB.SQL" For Output As #J
 For Each Td In Db.TableDefs
  If Not Left(Td.Name, 4) = "MSys" Then
   B = True
   Print #J, "DROP TABLE " & Td.Name & ""
   Print #J, "GO"
   Print #J, "CREATE TABLE " & Td.Name & "("
   For Each F In Td.Fields
    If Not B Then
     Print #J, ","
    End If
    B = False
    Print #J, " " & F.Name & " ";
    Select Case F.Type
     Case 1 'Boolean
      Print #J, "bit";
     Case 4 'Numeric
      Print #J, "int";
      If F.Attributes = 17 Then
       Print #J, " IDENTITY(1,1)";
      End If
     Case 5 'Currency
      Print #J, "money";
     Case 7 'double
      Print #J, "decimal";
     Case 8 'DateTime
      Print #J, "datetime";
     Case 10 'Text
      Print #J, "varchar(" & F.Size & ")";
     Case 12 'Memo
      Print #J, "text";
     Case Else
      Print #J, "Unknown: " & F.Type;
      Debug.Print "Unknown: " & F.Type & ": " & F.Name
      '1=boolean
      '4=numeric
      '10=text
    End Select
   Next
   Print #J,
   Print #J, ")"
   Print #J, "GO"
  End If
 Next
 If True Then
  For Each Td In Db.TableDefs
   If Not Left(Td.Name, 4) = "MSys" Then
    Set Rs = Db.OpenRecordset("SELECT * FROM " & Td.Name)
    Print #J, "SET IDENTITY_INSERT " & Td.Name & " ON"
    Print #J, "GO"
    While Not Rs.EOF
     SQL = ""
     For I = 0 To Rs.Fields.Count - 1
      SQL = SQL & ", " & Rs.Fields(I).Name
     Next
     SQL = "INSERT INTO " & Td.Name & " (" & Mid(SQL, 3) & ") VALUES ("
     For I = 0 To Rs.Fields.Count - 1
      If Rs.Fields(I).Type = 12 Then
       If IsNull(Rs.Fields(I)) Then
        lstr = ""
       Else
        lstr = Rs.Fields(I)
       End If
       SQL = SQL & "'" & Replace(lstr, vbCrLf, "<br />") & "'"
      ElseIf Rs.Fields(I).Type = 10 Then
       SQL = SQL & "'" & Rs.Fields(I) & "'"
      ElseIf Rs.Fields(I).Type = 5 Then
       SQL = SQL & Replace(Rs.Fields(I), ",", ".")
      ElseIf Rs.Fields(I).Type = 1 Then
       SQL = SQL & Replace(Replace(Rs.Fields(I), "Onwaar", "0"), "Waar", "1")
      ElseIf IsNull(Rs.Fields(I).Value) Then
       SQL = SQL & "0"
      Else
       SQL = SQL & Rs.Fields(I)
      End If
      If I < Rs.Fields.Count - 1 Then
       SQL = SQL & ", "
      End If
     Next
     SQL = SQL & ")"
     Print #J, SQL
     Rs.MoveNext
    Wend
    Print #J, "SET IDENTITY_INSERT " & Td.Name & " OFF"
    Print #J, "GO"
    Rs.Close
    Set Rs = Nothing
   End If
  Next
 End If
 Close J
End Sub

[topr8]

we redesigned the whole system at the same time.

the reason being that sql server is much more powerful and you can do things different ways.

the straight answer is that it is not a trouble free straightforward switch over.